Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner - Creator
Partner - Creator

Delta Load based on multiple Conditions and generate QVD with different names

Hello All,

I have my Source data in the following structure.

IDpart Info
1/year=2019/month=09/day=17/hour=16/
2/year=2019/month=09/day=17/hour=16/
3/year=2019/month=09/day=17/hour=16/
4/year=2019/month=09/day=17/hour=17/
2/year=2019/month=09/day=17/hour=17/
3/year=2019/month=09/day=17/hour=17/
1/year=2019/month=09/day=18/hour=12/
2/year=2019/month=09/day=18/hour=12/
1/year=2019/month=09/day=19/hour=17/

 

I would like to perform a Delta load and save each partinfo as a QVD file. The QVD files should have the data like in the below table .

IDpart InfoQVD Name
1/year=2019/month=09/day=17/hour=16/data_2019091716.qvd
2/year=2019/month=09/day=17/hour=16/data_2019091716.qvd
3/year=2019/month=09/day=17/hour=16/data_2019091716.qvd
4/year=2019/month=09/day=17/hour=17/data_2019091717.qvd
2/year=2019/month=09/day=17/hour=17/data_2019091717.qvd
3/year=2019/month=09/day=17/hour=17/data_2019091717.qvd
1/year=2019/month=09/day=18/hour=12/data_2019091812.qvd
2/year=2019/month=09/day=18/hour=12/data_2019091812.qvd
1/year=2019/month=09/day=19/hour=17/data_2019091917.qvd

 

Basically, i have so far generated Date Time (Ex: 20190917-16) from the part Info field and tried to paas it on as a Variable in the select statement and also in the Store qvd Statement.

Also, each time the Delta Load should only fetch records which are not already stored in any of the QVDs.

Let's say the baove table is already saved as QVDs with names as mentioned in the QVD Name column. And there is a new entry in the source table 

IDpart Info
1/year=2019/month=09/day=19/hour=23/
2/year=2019/month=09/day=20/hour=10/

 

Now the QVD Generator should only read both these records and generate two new QVD files.

IDpart InfoQVD Name
1/year=2019/month=09/day=19/hour=23/data_2019091923.qvd
2/year=2019/month=09/day=20/hour=10/data_2019092010.qvd

 

My QVD Generator has to do two things here: Chekc for part Info in Source Table which is greater than the existing QVDs and save the data with similar part Info. in a single QVD file.

Please find attached Sample Application. Any ideas would be really helpful.

 

Thanks and Best Regards

Sai.

Labels (1)
7 Replies
Sai33
Partner - Creator
Partner - Creator
Author

Was facing problem attaching my sample data to the original post. Hence, the attachment here. 

Sai33
Partner - Creator
Partner - Creator
Author

@sunny_talwar  @marcus_sommer 

Hoping you people have some valuable inputs here. 

Thanks.

Sai33
Partner - Creator
Partner - Creator
Author

Anyone has a quick suggestion here. Would be really helpful for a pleasant weekend :')

Sai33
Partner - Creator
Partner - Creator
Author

This issue is still bugging me a bit. Any one has any suggestion here

patilamay
Contributor III
Contributor III

Hi Sai,

Here's what you can do. 

I guess you are able to store QVDs separately based on date you extracted.

Once you store all the qvds separately. Store them in one qvd just include the date field in it that you've extracted from files.  For Ex A.qvd (this has all the dates in it)

Then extract max date from that QVD and store in a variable. Drop the table. Look at below code.

Temp:

Load distinct Date

From *.qvd;

Store Temp into A.qvd (QVD);

Drop table Temp;

 

MaxDate:
LOAD max(Date) as MaxDate
FROM A.qvd
(qvd);

Let vMaxDate = Peek('MaxDate',0,'MaxDate');

Drop table MaxDate;

For Delta load you can do the following - 

Run throw each file using for loop. Extract date again from the file. Compare it with vMaxDate variable in an if condition, if it's greater, then load that file and store into QVD.

For each vFile in FileList(*.xls);

Let vFileDate =  use your code for extracted date ;

if $(vFileDate) > $(vMaxDate) then

File:

Load *, [use your code for date] as Date

From  [$(vFile)]

where [use your code for date] > $(vMaxDate) 

Store File into File_date.qvd (qvd);

next vFile;

 

Hopefully this works.

 

Thanks,

 

Sai33
Partner - Creator
Partner - Creator
Author

Thanks Patilamay,

Your suggestion has helped me almost finish the issue.

But,  For each vFile in FileList(*.xls);

Here i load my Data from a DataLake and not sure how can i define the vFile. 

patilamay
Contributor III
Contributor III

Hi Sai,

vFile will get initiated automatically when run in For loop. Just point it datalake folder path.

For each vFile in FileList(datalakepath\*.xlsx)

 

Thanks,