topic Re: Loop syntax in App Development

Loop syntax

desert_dweller — Sun, 06 Aug 2023 22:52:24 GMT

Hive mind!

I have a requirement to create a table with one row for every date with various details from a resident table.

I need help with syntax of:

for every day between Min Date and Max Date in resident Transaction table (including days for which there are no transactions)

Load

sum(transaction amount),

loop date,

dimension

resident transaction

where transaction date<=loop date

group by dimension

next date;

Thanks in advance for any help!!

Re: Loop syntax

afurtado — Mon, 07 Aug 2023 00:49:57 GMT

You could use iterno() https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterFunctions/IterNo.htm

See my example

Table_Tmp:
LOAD * inline [
transaction amount, date , loop date
100 , 01/01/2023 , 10/01/2023
140 , 01/02/2023 , 10/02/2023
];

Final_Table:
LOAD
"transaction amount",
date("date"+(IterNo()-1)) as "date"
Resident Table_Tmp While "date"+(IterNo()-1) <= "loop date";
//
Drop Table Table_Tmp;

Re: Loop syntax

desert_dweller — Sun, 13 Aug 2023 21:55:03 GMT

Thank you, this pointed me in the right direction!