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]

Can you send me that qvw please?? Honestly it isn't working at all on my side. I've used this script:

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;

And I get nothing:

[andrescc]

Hi,

This is the script:

Temp:
LOAD ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
Num(REPORT_DATE) as NUM_REPORT_DATE
FROM
[Data.xlsx]
(ooxml, embedded labels, table is Data);

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

NoConcatenate
Report:
LOAD
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
NUM_REPORT_DATE
RESIDENT Temp
WHERE Exists(MAX_DATE,NUM_REPORT_DATE);
//WHERE NUM_REPORT_DATE = $(vMaxDate);

DROP TABLES Temp;

Regards,

ACC

[khaycock]

You are right, the script worked great in my test file! 

However...

When I move the scripting over to my dashboard it doesn't like it. Is there anything in this script which would stop the Max Date from happening?

ItemHistoricUsageTemp:
LOAD // ORGANIZATION_ID,
// INVENTORY_ITEM_ID,
INVENTORY_ITEM_ID & '_' & ORGANIZATION_ID AS %KEY1,
PERIOD_INFO as HistPeriodInfo,
num(REPORT_DATE) as NumDate,
UNIT_COST,
TOTAL_USAGE,
MOVES_ABC,
ITEM_TYPE,
PICK_CLASS,
CYCLE_CLASS,
CYCLE_COUNT,
RTG_TIME,
if((TOTAL_USAGE) <0, 'Yes', 'No') as [12MonthUsageFlag]
FROM
[ItemHistoricUsageReport.qvd]
(qvd);

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

NoConcatenate
ItemHistoricUsage:
LOAD
// ORGANIZATION_ID,
// INVENTORY_ITEM_ID,
INVENTORY_ITEM_ID & '_' & ORGANIZATION_ID AS %KEY1,
PERIOD_INFO as HistPeriodInfo,
REPORT_DATE as HistReportDate,
UNIT_COST,
TOTAL_USAGE,
MOVES_ABC,
ITEM_TYPE,
PICK_CLASS,
CYCLE_CLASS,
CYCLE_COUNT,
RTG_TIME,
if((TOTAL_USAGE) <0, 'Yes', 'No') as [12MonthUsageFlag],
NumDate
RESIDENT ItemHistoricUsageTemp
WHERE Exists(MAX_DATE,NumDate);
DROP TABLE ItemHistoricUsageTemp;

[andrescc]

Hi,

Sure, the field REPORT_DATE doesn't exist in you script, you create num(REPORT_DATE) as NumDate, but forgot  the field REPORT_DATE
The first part of you script should be like this:

ItemHistoricUsageTemp:
LOAD // ORGANIZATION_ID,
// INVENTORY_ITEM_ID,
INVENTORY_ITEM_ID & '_' & ORGANIZATION_ID AS %KEY1,
PERIOD_INFO as HistPeriodInfo,
num(REPORT_DATE) as NumDate,

REPORT_DATE,

UNIT_COST,
TOTAL_USAGE,
MOVES_ABC,
ITEM_TYPE,
PICK_CLASS,
CYCLE_CLASS,
CYCLE_COUNT,
RTG_TIME,
if((TOTAL_USAGE) <0, 'Yes', 'No') as [12MonthUsageFlag]
FROM
[ItemHistoricUsageReport.qvd]
(qvd);

Regards,

ACC

[khaycock]

Yes that was it thank you!

Unfortunately I've realised an issue though. There are multiple different reports at different times on that date and I don't believe the MAX is pulling in every single one, only the final report of the day. Is there a way to combine all reports on the max date with different report times?

[andrescc]

Do you need all reports of the last day? for example all reports of 2/4/2019?

Regards,
ACC

[khaycock]

Yes all reports on the last day so all reports from 2/4/2019

[andrescc · Accepted solution]

Try changing Num() and Max() for Floor(Num( )) and Floor(Max()).
Script with changes:

Temp:
LOAD ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
Floor(Num(REPORT_DATE)) as NUM_REPORT_DATE
FROM
[Data.xlsx]
(ooxml, embedded labels, table is Data);


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




NoConcatenate
Report:
LOAD
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
NUM_REPORT_DATE
RESIDENT Temp
WHERE Exists(MAX_DATE,NUM_REPORT_DATE);


DROP TABLES Temp;

Regards,
ACC