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: 
Anonymous
Not applicable

Edit Script Max Value

Hi Guys,

I have seen a few similar discussions but haven't found the exact answer I'm looking.

I have the below QVD brought into QLIK using the Edit Script

  LOAD EFF_DT,
date(floor(EFF_DT)) as EFF_DT_DAY,
year(EFF_DT) as EFF_DT_YEAR,
Month(EFF_DT) as EFF_DT_MONTH,
date(Monthstart(EFF_DT),'YYYY MMM') as EFF_DT_YEARMONTH,
max(PRODUCT_CD), 
MANUFACTURER_ID
FROM
[SAMPLE.qvd] 

 

Each Manufacturer_ID can have numerous Product_CDs with differing EFF_DTs, However I only want the Edit Script to bring in the latest PRODUCT_CD per Manufacturer_ID. I've tried to do this using the Max function but this doesn't seem to work.

Any ideas where I'm going wrong?

Cheers,

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Sample: 

LOAD EFF_DT, 

  date(floor(EFF_DT)) as EFF_DT_DAY, 

  year(EFF_DT) as EFF_DT_YEAR, 

  Month(EFF_DT) as EFF_DT_MONTH, 

  date(Monthstart(EFF_DT),'YYYY MMM') as EFF_DT_YEARMONTH, 

  PRODUCT_CD, 

  MANUFACTURER_ID 

FROM [SAMPLE.qvd]; 

 

Inner Join (Sample) 

LOAD Max(EFF_DT) As EFF_DT, 

  FirstSortedValue(PRODUCT_CD, -EFF_DT) as PRODUCT_CD

  MANUFACTURER_ID 

Resident Sample 

GROUP BY MANUFACTURER_ID;

View solution in original post

6 Replies
sunny_talwar

Try this:

Table:

LOAD EFF_DT,
          Date(floor(EFF_DT)) as EFF_DT_DAY,
          Year(EFF_DT) as EFF_DT_YEAR,
          Month(EFF_DT) as EFF_DT_MONTH,
          Date(Monthstart(EFF_DT),'YYYY MMM') as EFF_DT_YEARMONTH,
          PRODUCT_CD, 
          MANUFACTURER_ID
FROM [SAMPLE.qvd] (qvd);


Right Join (Table)

LOAD MANUFACTURER_ID ,

    Max(PRODUCT_CD) as PRODUCT_CD

Resident Table

Group By MANUFACTURER_ID;

jonathandienst
Partner - Champion III
Partner - Champion III

Perhaps this:

Sample:

LOAD EFF_DT,

  date(floor(EFF_DT)) as EFF_DT_DAY,

  year(EFF_DT) as EFF_DT_YEAR,

  Month(EFF_DT) as EFF_DT_MONTH,

  date(Monthstart(EFF_DT),'YYYY MMM') as EFF_DT_YEARMONTH,

  PRODUCT_CD,

  MANUFACTURER_ID

FROM [SAMPLE.qvd];

Inner Join (Sample)

LOAD Max(EFF_DT) As EFF_DT,

  PRODUCT_CD,

  MANUFACTURER_ID

Resident Sample

GROUP BY PRODUCT_CD,

  MANUFACTURER_ID;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Hi Sunny,

This doesn't seem to bring anything back for the PRODUCT_CD field.

Tim

Anonymous
Not applicable
Author

Hi Jonathan,

This is very close, however it still returning several duplicates i.e MANUFACTURER_ID returning with more than one PRODUCT_CD.

sunny_talwar

May be this:

Sample: 

LOAD EFF_DT, 

  date(floor(EFF_DT)) as EFF_DT_DAY, 

  year(EFF_DT) as EFF_DT_YEAR, 

  Month(EFF_DT) as EFF_DT_MONTH, 

  date(Monthstart(EFF_DT),'YYYY MMM') as EFF_DT_YEARMONTH, 

  PRODUCT_CD, 

  MANUFACTURER_ID 

FROM [SAMPLE.qvd]; 

 

Inner Join (Sample) 

LOAD Max(EFF_DT) As EFF_DT, 

  FirstSortedValue(PRODUCT_CD, -EFF_DT) as PRODUCT_CD

  MANUFACTURER_ID 

Resident Sample 

GROUP BY MANUFACTURER_ID;

Anonymous
Not applicable
Author

Hi Sunny,

That's it working!

Thanks,