Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator II

Daily 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

5 Replies
bramkn
Partner - Specialist
Partner - Specialist

first you need to get the min and max value of the dates. for example by sorting it and then use peek to get the first(0) and last(-1) value. then do a for min to max

within that for loop resident where date = the for counter. and store into qvd with the counter ar the end of the name.

andrei_delta
Partner - Creator III
Partner - Creator III

Hi,

Sadly the format you want for the date is not supported when saving qvd so maybe you can think of a different output like 09_04_2018 and your system date is a bit tricky but i managed to do it.

Don't forget to put your own paths!

Regards,

Andrew

mahitham
Creator II
Creator II
Author

Hi Andrei,

Could you please share the script or qliksense qvf file.

I don't have Qlikview license.

Thanks in advance.

lawrance
Creator II
Creator II

Hi Mahitha,

Try this link: How to store individual QVD based on field value

Thanks,

Lawrance A

andrei_delta
Partner - Creator III
Partner - Creator III

ok.

let vPath='.\loop\';

Temp1:

Load *,

MakeDate(Year,Month,Day) as DateNew;

LOAD Date,

'20'&right (Date,2) as Year,

if(len(Date)<6,mid( Date,2,2 ),mid( Date,3,2 )) as Month,

if(len(Date)<6,left(Date,1),left(Date,2)) as Day

FROM

[.\QLIK.xlsx]

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

Extractions:

LOAD Distinct Date , DateNew

Resident Temp1;

Drop table Temp1;

let vNoOfRows= NoOfRows('Extractions');

for i=0 to $(vNoOfRows)-1

let vDBDate=peek('Date',i,'Extractions');

let vNewDate=text(peek('DateNew',i,'Extractions'));

Temp2:

NoConcatenate

LOAD Client,

     Rank,

     Date,

     Products

FROM

[.\loop\QLIK.xlsx]

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

where Date=$(vDBDate);

let vQVD= vPath & 'QLIK_'& vDBDate;

STORE Temp2 into [$(vQVD).qvd] (qvd);

drop Table Temp2;

next i;

drop table Temp1;