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: 
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

Do you want to keep the max date for every dimension, or do you want to pick the date with the maximum amount for each period?

Peter

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Also, and to avoid misunderstandings, can you add a table with the required output for this particular data sample? Thanks,

Peter

manoranjan_d
Specialist
Specialist
Author

pick the data with maximum date only.

i will say an example in the data

  

KeyCreated dateamount
Key110-Oct-16100
Key111-Oct-162000
Key112-Oct-16300
Key113-Oct-1615456
Key114-Oct-161564

the key1 has been repeated 5 times in a week , so the key count should not be 5 times, it should be count as 1 with max date

so the output for the oct xth week is key1 14oct2016 1564 .

similar to month, quater and year.

Kushal_Chawda

Data:

LOAD Key,

           CreatedDate,

           Amount

FROM table;


left join(Data)

LOAD key,

           date(max(CreatedDate)) as CreatedDate,

             1 as MaxDateFlag

resident Data

group by key;

Final:

noconcatenate

LOAD *

resident Data

where MaxDateFlag=1;

drop table Data;

         

manoranjan_d
Specialist
Specialist
Author

if the key1 is repeating in xth week of 2015 and xth of 2016, i need the max date for both years.

manoranjan_d
Specialist
Specialist
Author

this is only for the date max i need the separate table for week,month quater and year.

Kushal_Chawda

Why you need separate table?

Just try to create the field in table itself

Data:

LOAD Key,

           CreatedDate,

           Amount

FROM table;


left join(Data)

LOAD key,

           date(max(CreatedDate)) as CreatedDate,

             1 as MaxDateFlag

resident Data

group by key;

Final:

noconcatenate

LOAD *,

          Year(CreatedDate) as Year,

          month(CreatedDate) as Month,

          Week(CreatedDate) as Week,

          'Q' & ceil(month(CreatedDate)/3) as Quarter

resident Data

where MaxDateFlag=1;

drop table Data;

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Something like this?

I don't manage to put everything in one table because week numbers may cross month borders. Two tables at the least. You can proceed from here, as I don't think this can be used as-is in whatever document you're preparing.

Date granularity in script thread218975.jpg

Best,

Peter

manoranjan_d
Specialist
Specialist
Author

Hi chawda

i fu put max date then it ll take only one date, suppose if the key 1 is repeating on the next month (nov) then  it ll take only the november month only, then it wont take the oct weeks max date.