topic Re: Edit Script Max Value in QlikView

Edit Script Max Value

Anonymous — Thu, 11 Feb 2016 12:03:44 GMT

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,

Re: Edit Script Max Value

sunny_talwar — Thu, 11 Feb 2016 13:06:40 GMT

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;

Re: Edit Script Max Value

jonathandienst — Thu, 11 Feb 2016 13:09:37 GMT

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;

Re: Edit Script Max Value

Anonymous — Thu, 11 Feb 2016 13:52:18 GMT

Hi Sunny,

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

Tim

Re: Edit Script Max Value

Anonymous — Thu, 11 Feb 2016 13:53:47 GMT

Hi Jonathan,

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

Re: Edit Script Max Value

sunny_talwar — Thu, 11 Feb 2016 14:23:24 GMT

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;

Re: Edit Script Max Value

Anonymous — Thu, 11 Feb 2016 14:41:44 GMT

Hi Sunny,

That's it working!

Thanks,