Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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;
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;
Hi Sunny,
This doesn't seem to bring anything back for the PRODUCT_CD field.
Tim
Hi Jonathan,
This is very close, however it still returning several duplicates i.e MANUFACTURER_ID returning with more than one PRODUCT_CD.
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;
Hi Sunny,
That's it working!
Thanks,