Qlik doesn't recognize a Date variable

[Rania]

Hello Community,

I have a loop on dates, then in my transformation I need to get the data of the previous date.

// Getting the distinct dates from the data source

Dates_Table:

load distinct            date(floor(Eventdate),'DD/MM/YYYY') as EVENT_DATE          FROM Source;

//Loop on EVENT_DATE

FOR i = 0 to NoOfRows('Dates_Table')-1
LET vDate = peek('EVENT_DATE', i, 'Dates_Table');

Load
Location,
GTIN,
ClosingQty as OpeningStock

Resident Source;

WHERE date(Eventdate,'DD/MM/YYYY')=date('$(vDate )','DD/MM/YYYY')-1;

trace ($(vDate ));
next i

The trace shows the correct dates but the result of the loop is Empty so I tried to replace the variable in the where clause by tapping the date('06/01/2022') the loop worked successfully.

My issue now is that the where clause doesn't get the variable vDate, yet when I enter the date by myself it works.

Thank you,

[marcus_sommer]

You may change it in this way:

Dates_Table:
load distinct  floor(Eventdate) as EVENT_DATE FROM Source;

FOR i = 0 to NoOfRows('Dates_Table')-1
LET vDate = peek('EVENT_DATE', i, 'Dates_Table') - 1;

Load
Location,
GTIN,
ClosingQty as OpeningStock

Resident Source;

WHERE floor(Eventdate) = $(vDate);

trace ($(ClosingDate));
next i

- Marcus

[PrashantSangle]

where you are defining ClosingDate?

also confirmed that vDate is taking correct date format as per your format in table.

Regards,

Prashant Sangle

[Rania]

Hi Prashant Sangle,

I meant the variable "vDate " not "ClosingDate".

Yes, It's the same format as in the table.

[Rania]

It didn't work 😞 

[marcus_sommer]

What didn't work? No records within the table: Dates_Table or the loop didn't iterate or the where clause returned never any TRUE or vDate is empty or didn't contained a number or any error ... ?

- Marcus

[Rania]

The same issue ,no error , the where clause doesn't understand the content of the variable ' $(vDate )'

[marcus_sommer]

What does $(vDate) contain? How it's called '$(vDate)' or $(vDate)? Further be aware that in your example there is by $(vDate ) an extra space on the end which needs to removed within the call.

- Marcus

[Rania]

According to my script :

FOR i = 0 to NoOfRows('Dates_Table')-1
LET vDate = peek('EVENT_DATE', i, 'Dates_Table');

//

//

next i;

the variable $(vDate) contains one date for each iteration.

I tried $(vDate) and '$(vDate)' and it doesn't work for both of them.

[marcus_sommer]

Try a change to:

FOR i = 0 to NoOfRows('Dates_Table')-1
LET vDate = peek('EVENT_DATE', $(i), 'Dates_Table');

//

//

next i;