topic Date formatting variable load script in QlikView

Date formatting variable load script

mp802377 — Sat, 16 Nov 2024 18:03:48 GMT

Hello, I have this script below. You can see the vFileSuffix is setting the dataformat as YYYYMMDD. But when it saves, it is saving like this: DOLLAR_A_DAILY_LOGS_572080329.QVD. The vDate for example is 20200922, but the vFileSuffix is 572080329. I want the file to be stored as DOLLAR_A_DAILY_LOGS_20200922.QVD

FOR Each vDate in $(vDateList)

Let vFileSuffix = Date('$(vDate)','YYYYMMDD');

Data:
LOAD *;
SQL SELECT *
FROM HIVE.table.`fact_table`
Where $(vWhereStatement);

Store Data into [$(vQVDFiles)\UAPI_HOURLY_LOGS_$(vFileSuffix).QVD] (QVD);
Drop Table Data;

NEXT vDate;

I have tried changing the Let vFileSuffix = Date('$(vDate)','YYYYMMDD'); to Let vFileSuffix = Date('$(vDate)','YYYY-MM-DD');, still didn't work.

I had first tried no formatting since the vDate comes in like vDate 20200922 (this is copied from debugging) like this vFileSuffix = Date('$(vDate). That didn't work either

Please help

Re: Date formatting variable load script

Taoufiq_Zarra — Thu, 24 Sep 2020 17:56:57 GMT

@mp802377 try this ?

FOR Each vDate in $(vDateList) Let vFileSuffix = Date#('$(vDate)','YYYYMMDD'); Data: LOAD *; SQL SELECT * FROM HIVE.table.`fact_table` Where $(vWhereStatement); Store Data into [$(vQVDFiles)\UAPI_HOURLY_LOGS_$(vFileSuffix).QVD] (QVD); Drop Table Data; NEXT vDate;

Re: Date formatting variable load script

mp802377 — Thu, 24 Sep 2020 19:19:26 GMT

Oh my word. I can't believe I missed the #. Thank you!

Martha