Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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
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;
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
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?
Yes all reports on the last day so all reports from 2/4/2019