Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
manoranjan_d
Specialist
Specialist

date granularity in script and storing as table

KeyCreated dateamount
Key110-Oct-16100
Key111-Oct-162000
Key112-Oct-16300
Key113-Oct-1615456
Key114-Oct-161564
key215-Oct-1645455
key216-Oct-16100
key210-Nov-162000
key211-Nov-16300
key212-Nov-1615456
key213-Nov-161564
key314-Nov-1645455
key315-Nov-16100
key316-Nov-162000
key317-Nov-16300
key318-Nov-1615456
key319-Nov-161564
Key110-Oct-15100
Key111-Oct-152000
Key112-Oct-15300
Key113-Oct-1515456
Key114-Oct-151564
key215-Oct-1545455
key216-Oct-15100
key210-Nov-152000
key211-Nov-15300
key212-Nov-1515456
key213-Nov-151564
key314-Nov-1545455
key315-Nov-15100
key316-Nov-152000
key317-Nov-15300
key318-Nov-1515456
key319-Nov-151564

hi above is my data,

I need the distinct key with max date for the week , for the month , for the quarter , for the year.

These (week, month, quater and year)  has to be retrieved  form the above table and these has to stored  as separate table as week, year, quarter, month.

can you help me how these can achieved in the script level

16 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

If you want three different tables, replace the JOIN of the second GROUP BY LOAD with a new table name and give the Month and Year fields unique names.

Best,

Peter

manoranjan_d
Specialist
Specialist
Author

peter u r almost right there but quater is missing, let me check this,

below s the output required.

   

KeyCreated dateamountWeeknumber
Key114-Oct-161564xx week
Key114-Oct-151564xx week
key215-Oct-1645455xx week
key216-Oct-16100xx week
key212-Nov-1615456xx week
key213-Nov-161564xx week
key216-Oct-15100xx week
key213-Nov-151564xx week
key319-Nov-161564xx week
key314-Nov-1545455xx week
key319-Nov-151564xx week

   

Quarter output
KeyCreated dateamount
Key114-Oct-161564
Key114-Oct-151564
key213-Nov-161564
key213-Nov-151564
key319-Nov-161564
key319-Nov-151564

  

Year output
KeyCreated dateamount
Key114-Oct-161564
Key114-Oct-151564
key213-Nov-161564
key213-Nov-151564
key319-Nov-161564
key319-Nov-151564
manoranjan_d
Specialist
Specialist
Author

month output
KeyCreated dateamount
Key114-Oct-161564
Key114-Oct-151564
key216-Oct-16100
key213-Nov-161564
key216-Oct-15100
key213-Nov-151564
key319-Nov-161564
key319-Nov-151564
manoranjan_d
Specialist
Specialist
Author

peter i cant see the quater wise data

Peter_Cammaert
Partner - Champion III
Partner - Champion III

All data matches, except for the weeks. How do you calculate the week number? Not by using 'x'-numbers I guess

manoranjan_d
Specialist
Specialist
Author

I thought the week start from Sunday, so it got mismatch, i agree that week number is calculated from monday,

I cant take manually calculation for the weeknumber, so to show the output i just shown it as xxx 

can I know quaterly wise max date data?

manoranjan_d
Specialist
Specialist
Author

Hi peter,

I m waiting for your reply