Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table (Table1) with the following columns:
Job number | Start date | End date | Reference date | Remaining job amount | Total job days
Remaining job amount: monetary value that signals how much company still needs to pay for the job
Total job days: total number of days that the job is running; calculcated by end date - start date + 1
I want to create a loop that adds 14 new columns to the table. The column names should be the next 14 month-ends starting from the Refence date. The loop should calculate and assign what remaining job amount is to be paid in the next 14 months, so the company can see what payments are due for those next 14 months.
I do this by calculating the number of days for the 14 next months, divide this number by the total job days and multiply it by the remaining job amount.
I wrote the following FOR loop for this:
LET vStartDate = MakeDate(2024, 09, 30);
LET vEndDate = MakeDate(2025, 10, 31);
LET vMaxColumns = 14;
LET vColumnCount = 0;
SET vColumns = '';
FOR vDate = vStartDate TO vEndDate STEP 1
IF Day(vDate) = Day(MonthEnd(vDate)) THEN
LET vDateFormatted = Date(vDate, 'DD-MM-YYYY');
LET vCurrentColumn =
'max(0, min([Start date], Date(' & vDate & ')) - ' &
'max([Start date], Date(' & vDate & ') - Day(Date(' &
vDate & ')) + 1) + 1) * [Remaining job amount] /
[Total job days] ' &
'AS [Month' & vDateFormatted & ']';
LET vColumns =
If(Len(Trim(vColumns)) > 0, vColumns & ', ', '') &
vCurrentColumn;
LET vColumnCount = vColumnCount + 1;
TRACE Generated column: Month_$(vDateFormatted);
// Stop loop if maximum # columns has been reached
IF vColumnCount >= vMaxColumns THEN
EXIT FOR;
ENDIF
ENDIF
NEXT vDate
NoConcatenate
Table2:
LOAD
[Job number],
[Start date],
[End date],
[Reference date],
[Remaining job amount],
[Total job days],
$(vColumns)
RESIDENT Table1;
By adding the TRACE statement I can see in the log that Qlik is not exiting the loop properly and is not moving on to the next part of the script when it hits the maximum of 14 columns. So, it is stuck at creating the last month-end column 'Month_31-10-2025'.
I suspect it has to do with my ending of the loop; but I cannot figure out exactly what it is.
I'm storing a daily record of a company backlog. However, I can't figure out how not to get duplicates if the script loads multiple times one day. Can someone guide me in the right direction?
I've tried where not exists() with no success.
Here's the code.
Data:
LOAD
%client,
"# Net Amount Order",
Date(now(),'YYYY-MM-DD') as DateStamp
FROM [lib://en-US (ad_ext_kons)/Business Documents.qvd](qvd) ;
Left Join(Data)
Load
%client,
sum("# Net Amount Order") as sum
resident Data
group by %client;
Concatenate (Data)
Load %client,
sum,
DateStamp
FROM [lib://QLIKDATA/QVD\GG\Orderstock99.qvd] (qvd);
Store
%client,
sum,
DateStamp
from Data into [lib://QLIKDATA/QVD/GG/]Orderstock99.qvd(QVD);
Hi
I am trying to send a API request into Qrs,
The purpose is to save the last click of the user about some topic like language, date format etc.
When user clicks on its preference, I want to save it into custom property (in the qrs?), and every running I will read it into var, and when the app is loaded the selection will present.
I am spend a lot of time with the documentation, foroms etc. any help will be appropriate.
Thanks
Hi,
I have a container with 2 charts that are displayed based on a specific condition. At any given time, only one chart will be shown.
The first chart shows income vs expenses, shows the last 3 months based on the maximum date.
The second chart shows income vs forecasted income, the data displaying according to the selected period.
Both charts use the same dimension - Date.
Both charts are functioning and displaying accurate data.
I trying to "merge" them and add the condition to the measures.
The problem is that when I want to display "the first chart", it shows data of the second chart and only for the current month, while the others show 0.
This is one of the measures:
If(Count(distinct YearMonth) <> 1,
[Forecast], // chart 2
{<Date={">=$(v3Months_StartDate)<=$(v3Months_EndDate)"}, Year=, Quarter=, MonthID=>} [Expenses] // chart 1
) / 1000000
The condition refers to the number of selected months. Even though one month is selected (count(distinct YearMonth) = 1), the forecast is displaying instead of the expenses.
Why is this happening?
I would appreciate your help.
Thanks.
Dear all,
After upgrading my Talend studio version (to R2024-09) I'm facing an issue only when I deploy a microservices to expose an API.
My API return a 500 error with this message : org/talend/transform/runtime/common/MapExecutor
The job run well when I'm testing it in the Studio, the issue occurs only when deployed on Talend Runtime.
Here the things I've tried:
- change my job and rewrite the tHmap
- upgrade Runtime and Studio to version R2024-10
- download last valid commit, import to Studio and deploy
Ofc talend-data-mapper and talend-data-mapper-dependencies are installed on Talend Runtime.
My last chance is to rewrite the job to avoid using tHmap...
Is someone have encountered this kind of issue ?
Dear All,
I have Table1 with InvoiceId, which I have to match with Id, TransId and PaymentId respectively in Table2. If InvoiceId from Table1 match with any one of these 3 columns, then show status as Matched. Else show not matched in a table. Please suggest.
Hi,
I have a sales table with products sold on different dates and a budget table with budget amounts (month wise) for all the products for 12 months (Apr'24-Mar'25).
I have made an association between both the tables by creating a composite key using product_code, monthyear and Region fields.
In front-end, when I click on ytd button, the sales amount is showing correctly while the Budget is only reflecting of those products of which sale has happened in sales table. But I want to also include budget of those products for which sale has not happened.
Hello Qlik Community,
Is there any information about adding Alignment (to left or to right) option to object Variable input from Qlik Dashboard Bundle? This object has only one option and always aligned to left.
In many of my apps I need aligning to right.
I'm trying to place the object and to adjust it to other objects so it looks good but still it's not always placed well enought.
Thanks
Miriam
Dear all, I have a table with general ledger movements, and I want a bar chart showing the sum of these amounts per month/year.
It's cumulative data, so the bar chart should show e.g. jan/2021 - feb/2021 - mar/2021 whereby the amount in each month is cumulative for the month in question and all previous months.
Below sample table of my data, with the column Desired Outcome what needs be shown in the bar chart.
YearMonth | Amount | Desired Outcome | |
2024-Jul | -98.164,39 | 88.433,27 | all data |
2024-Jun | 41.816,93 | 186.597,66 | all data up to jun 2024 |
2024-May | 13.591,33 | 144.780,73 | all data up to may 2024 |
2024-Apr | -228.636,43 | 131.189,40 | all data up to apr 2024 |
2024-Mar | 191.707,76 | and so forth…. | |
2024-Feb | -50.594,50 | ||
2024-Jan | 95.192,49 | ||
2023-Dec | -2.823,69 | ||
2023-Nov | -44.201,88 | ||
2023-Oct | 70.545,65 | ||
2023-Sep | 100.000,00 |
This is the formula I tried, with no succes.
Sum({< [YearMonth] = {"<=$(=Max(YearMonth))"} >} ActAmount) |
Can somebody help please.
Many thanks,
Paul
hi everyone,
I have this mapping load:
SEARCH_FIELD:
mapping LOAD* INLINE [
SEARCH , REPLACE
' I3','| I3'
' I2','| I2'
' I1','| I1'
' I4','| I4'
' I5','| I5']
as I want to replace these strings in the 1st column by the ones in the 2nd column using a MapSubString function as below:
TABLE:
LOAD MapSubString('SEARCH_FIELD',SEARCH) AS RESULT FROM MyData;
As you can see, there is a space at the beginning of the SEARCH elements ([space]I3 for instance) because I want the text to be replaced only if there is indeed a space before the "I". If there is no space, there shouldn't be a mapping.
However, the MapSubString (or the mapping load) is not considering that space, which leads me to substitute the data where no substitution should be done.
Do you have an idea on how I could fix this?
thanks a lot for your help!
Seamless Public Data Sharing with Qlik's New Anonymous Access Capability
Fuel your data and AI journey with the right services, delivered by our experts.
With Qlik Answers™, get personalized, GenAI-driven answers from unstructured data, so you can make decisions with confidence. Learn how in our product roadmap keynote and demo.
Browse our helpful how-to's to learn more about navigating Qlik Community and updating your profile.
Your journey awaits! Join us by Logging in and let the adventure begin.
Qlik enhances decision-making with high-speed insights, as Mayborn Group integrates data from various functions across their global operations, gaining a competitive edge in the childcare industry.
Nortera leads agricultural manufacturing analytics and automation with Qlik, reducing short-shipment rates and annual savings in water consumption.
Qlik Data Integration transforms Airbus' aircraft production, leading to over 150 replication tasks and informing more efficient analysis.
Join one of our Location and Language groups. Find one that suits you today!
A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights..
Qlik Communityの日本語のグループです。 Qlik製品に関する日本語資料のダウンロードや質問を日本語で投稿することができます。
Welcome to the group for Brazil users. .All discussions will be in Portuguese.
Hear from your Community team as they tell you about updates to the Qlik Community Platform and more!