Data Load

[khaycock]

I have a data set that is pulling many different reports into one set. However, I only want to show the data that is from the most recent report. 

Is there a way of restricting the data in the script to only show data that is from the most recent REPORT_DATE field? I can't hard code it to the date itself because this gets updated monthly.

[khaycock]

Still having the same issue with only PERIOD_INFO showing data

[andrescc]

Hi,

Try this:

Temp:
LOAD ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
SOURCING_SUPPLIER,
USE_DESC_TYPE
FROM
ItemHistoricUsageReport.qvd
(qvd);

Max_Date:
Load
PERIOD_INFO,
LastValue(REPORT_DATE) AS MAX_DATE
Resident Temp
Group by PERIOD_INFO
order by REPORT_DATE asc ;


NoConcatenate
Report:
LOAD
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
SOURCING_SUPPLIER,
USE_DESC_TYPE
RESIDENT Temp
WHERE Exist(MAX_DATE,REPORT_DATE);

DROP TABLES Temp,MAX_DATE;

Regards,
ACC

[khaycock]

Hey!

I changed the WHERE Exist to WHERE Exists and amended the drop table at the bottom to say Max_Date rather than MAX_DATE, but when it is reloaded, the data remains the same and I see all REPORT_DATE dates 😞

[andrescc]

Hi,

I'm sorry for the syntaxis error, try with this:

Temp:
LOAD ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
SOURCING_SUPPLIER,
USE_DESC_TYPE
FROM
ItemHistoricUsageReport.qvd
(qvd);

Table_Max_Date:
Load
Max(REPORT_DATE) AS MAX_DATE
Resident Temp;

LET vMaxDate=num(Peek('MAX_DATE',0,'Table_Max_Date'));


NoConcatenate
Report:
LOAD
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
SOURCING_SUPPLIER,
USE_DESC_TYPE
RESIDENT Temp
WHERE REPORT_DATE = $(vMaxDate);

DROP TABLES Temp,Table_Max_Date;

Regards,
ACC

[khaycock]

This returned no data whatsoever 😞

[andrescc]

Can you upload a qvw example?

[khaycock]

I'm not able to attach the QVD though

[andrescc]

upload an extract of qvd in excel format

[khaycock]

Oh yeah sure!

[andrescc]

This qvw works!!!

Regards,

ACC