Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have id,value,fromdate and todate fields. I want to get all the dates between these two fields.
When i use while loop
Date( FromDate + IterNo() - 1 ) as DateKey
While FromDate + IterNo() - 1 <= ToDate;
The output is:
Id value datekey
1 1 2019/03/01
1 1 2019/03/02
1 1 2019/03/03
The issue is for Id=1 the sum(value) should be 1 but because of while loop now the value is 3.
Please help.
Try this,
LOAD
$(varFrmDate) + Iterno()-1 As Num,
Date($(varFrmDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varFrmDate) + IterNo() -1 <= $(varToDate);
I would suggest have the date calender in another table, similar to mastr calender.
Try similar to below
table:
load id, value from table_name;
calender :
load id, datekey // use your code here , dont use value here
both the table should be joined by id.
-Pradosh
Can anyone help pls.
Maybe sum( firstsortedvalue(value,Date)) ??
Hello,
I have the same problem and need an solution in Qlik View!
Expample:
Table with Id, StartDate, endDate
Example Data
223, 01-Feb-22, 04-Feb-22
354, 21,Feb-22, 22-Feb-22
I want to display it now as follows, so listing the days between the startdate and enddate for each Id.
Data would be as follows:
223, 01-Feb-22
223, 02-Feb-22
223, 03-Feb-22
223, 04-Feb-22
354, 21-Feb-22
354, 22-Feb-22
The above shown while-loop will do the job - just adapt it to your fields:
load ID, Date( FromDate + IterNo() - 1 ) as DateKey
While FromDate + IterNo() - 1 <= ToDate;
Thanks for your help,
needed it to reload my origin table via resident, combinated with your code it works great. Many thanks!
Try this
Sum({<DateKey={'$(=Concat(Date(DateKey), ', '))'}>} Value)