Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
khaycock
Creator
Creator

Data Load

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.

Labels (1)
  • data

27 Replies
khaycock
Creator
Creator
Author

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:

empty.PNG

 

andrescc
Contributor III
Contributor III

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
Creator
Creator
Author

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;

date error.PNG

andrescc
Contributor III
Contributor III

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
Creator
Creator
Author

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?

report date.PNG

andrescc
Contributor III
Contributor III

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

Regards,
ACC
khaycock
Creator
Creator
Author

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

andrescc
Contributor III
Contributor III

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