Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mahitham
Contributor

Date wise QVD extraction

Hi Experts,

Can any one please help me to extract date wise qvds and to store qvd with QLIK_DD/MM/YYYY Format.

I have prepared sample excel file for explaining the requirement.

My date field is like DDMMYY Format in database.

The attached excel file contains three dates but in the real environment one qvd contains last 6 months data from this qvd need to extract one qvd for one date and need to store the qvd with date extension.

Please help me to do with for loop. Please find the attached excel file.

expected output:

1. QLIK_09/04/2018.qvd

QLIK_090418.png

2. QLIK_10/04/2018.qvd

QLIK_100418.png


Thanks in advance

 

1 Solution

Accepted Solutions

Re: Date wise QVD extraction

Change the environmental variable to this

SET DateFormat='DD-MM-YYYY';

and then run... I have changed it in the attached file.. can you try to run this now

6 Replies

Re: Date wise QVD extraction

Try this

Table:

LOAD Date(Date#(Date, 'DDMMYY')) as Date,

     prodcuts

FROM [..\..\Downloads\QLIK.xlsx]

(ooxml, embedded labels, table is [Daily extraction]);


FOR i = 1 to FieldValueCount('Date')


LET vDate = FieldValue('Date', $(i));

TempTable:

LOAD Client,

      Rank,

      Date(Date#(Date, 'DDMMYY')) as Date,

      prodcuts

FROM

[..\..\Downloads\QLIK.xlsx]

(ooxml, embedded labels, table is [Daily extraction])

Where Date#(Date, 'DDMMYY') = '$(vDate)';

STORE TempTable into QLIK_$(vDate).qvd (qvd);

DROP Table TempTable;

NEXT


DROP Table Table;

mahitham
Contributor

Re: Date wise QVD extraction

Hi Sunny,

Thanks a lot for your reply.

Sorry I for got to say I am working in Qliksense.

I have copied the same code in Qlik its generating the qvds but getting small issue here.

its generating qvds by creating folders  like below please let me know am I missed anything in the store command in the attached app.

and  need to generate qvd for each date. In the source have 3 dates but getting only 10 and 11 date qvds there is no 9 date qvd once new dates added for those dates also need to generate qvd.

folder.png

when I opened 10 folder

10.png

The default folder which have given to store qvds is C:\Daily extraction please help me am I missed any thing.

Here I have attached the app also.

tomasz_tru
Valued Contributor

Re: Date wise QVD extraction

Don't use / in file name

Re: Date wise QVD extraction

Instead of using /, may be use _ or something else to create your date field

mahitham
Contributor

Re: Date wise QVD extraction

Hi Sunny,

Thanks for your reply.

I did n't understand this / Instead of using /, may be use _ or something else to create your date field


could you please highlight in the below  code

Table:
LOAD Date(Date#(Date, 'DDMMYY')) as Date,
     prodcuts
FROM [lib://daily/QLIK.xlsx]
(ooxml, embedded labels, table is [Daily extraction]);

FOR i = 1 to FieldValueCount('Date')

LET vDate = FieldValue('Date', $(i));

TempTable:
LOAD Client,
      Rank,
      Date(Date#(Date, 'DDMMYY')) as Date,
      prodcuts
FROM [lib://daily/QLIK.xlsx]
(ooxml, embedded labels, table is [Daily extraction])
Where Date#(Date, 'DDMMYY') = '$(vDate)';

STORE TempTable into [lib://daily/QLIK_$(vDate).qvd](qvd);
DROP Table TempTable;

NEXT

Re: Date wise QVD extraction

Change the environmental variable to this

SET DateFormat='DD-MM-YYYY';

and then run... I have changed it in the attached file.. can you try to run this now

Community Browser