Edit Script Max Value

[Anonymous]

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,

[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]

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;

[Anonymous]

Hi Sunny,

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

Tim

[Anonymous]

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 · Accepted solution]

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]

Hi Sunny,

That's it working!

Thanks,