Loop syntax

[desert_dweller]

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!!

[afurtado · Accepted solution]

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

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;

[desert_dweller]

Thank you, this pointed me in the right direction!