Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
abc_18
Creator II
Creator II

How to write code to get 90 days data with max of each date dynamically

Hi All,

I have a date column in the table that also contains timestamp values, and that table's jobs run four times each day at the ETL end. As a result, the historical table has many partitions, which might potentially increase the size of the qvd.

Need help with writing code at the extract layer to retrieve data for 90 days with a maximum of each date is needed (max value from out of 4 partition in a day ).

Please suggest.

Labels (2)
3 Replies
Taoufiq_Zarra

@abc_18  can you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
abc_18
Creator II
Creator II
Author

Hello, Taoufiq

Here is a sample table; you can see that the "orderdate" column contains date values with various timestamps for the same day. Data provided in the table go back more than a year.

Now my requirement is to get data just for 90 days with a maximum of each date, need help in writing code at the extract layer.

For instance, the maximum value for the four partitions as of December 28, 2022, will be

2022-12-28T11:45:03.501+0000

id pollingstatus POdate thresholdvalue status orderdate
325375 null 2012-09-04T19:10:00.000+0000 No Disabled 2022-12-28T11:45:03.501+0000
231567 null 2014-08-19T18:25:00.000+0000 No Disabled 2022-12-28T11:35:03.501+0000
567890 null 2013-02-17T10:35:00.000+0000 No Enabled 2022-12-28T11:25:03.501+0000
325375 null null No Enabled 2022-12-28T11:20:03.501+0000
234567 null null No Enabled 2022-12-27T11:45:03.501+0000
325372 null null No Enabled 2022-12-27T11:40:03.501+0000
325370 null null No Enabled 2022-12-27T11:35:03.501+0000
Luis_Galvan
Contributor II
Contributor II

You could use a resident table with two/three columns: Date (date value from orderdate), orderdate, id (id field if you require to map the latest id key).

In the script you would do something like this

 

Maxtms:
Load
    Date(Date#(Left(orderdate,10),'YYYY-MM-DD')) 	as Date,
    Max(orderdate) 			as orderdate_recent_tms,
	Max(id)				as id_recent
resident [original table name]
Group by Date(Date#(Left(orderdate,10),'YYYY-MM-DD'));

 

 

... this will, deliberately, give you the latest TMS (timestamp) of each date, and its id value... now if you need the latest 90 days, you can apply where statement after resident and before group by

ex:

 

Where Date(Date#(Left(orderdate,10),'YYYY-MM-DD')) >= Today()-90

 

 

Hope this helps you with your challenge or gives you hints to have it resolved!

Cheers!

Luis G

BI Qlik Developer || QV || QS || Nprinting