Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

Still having the same issue with only PERIOD_INFO showing data

andrescc
Contributor III
Contributor III

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

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
Contributor III
Contributor III

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

This returned no data whatsoever 😞

andrescc
Contributor III
Contributor III

Can you upload a qvw example?


khaycock
Creator
Creator
Author

I'm not able to attach the QVD though

andrescc
Contributor III
Contributor III

upload an extract of qvd in excel format
khaycock
Creator
Creator
Author

Oh yeah sure!

andrescc
Contributor III
Contributor III

This qvw works!!!

Captura1000.JPG

Regards,

ACC