topic Re: Data Load in QlikView

Data Load

khaycock — Sat, 16 Nov 2024 04:22:53 GMT

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.

Re: Data Load

pradosh_thakur — Thu, 21 Feb 2019 09:14:48 GMT

1: get the max of date in a variable v_date max

2: In resident load use the noconacatenate where clasue to restrict the data.

3: Keep the resident table and delete the original table

Re: Data Load

khaycock — Thu, 21 Feb 2019 10:39:15 GMT

Do you have any idea why this Max Date field would be invalid?

Re: Data Load

pradosh_thakur — Thu, 21 Feb 2019 10:44:38 GMT

you need to use group by when you use any aggregation function like max,sum count etc.

Re: Data Load

khaycock — Thu, 21 Feb 2019 11:02:04 GMT

Does it matter which fields I group?

Re: Data Load

pradosh_thakur — Thu, 21 Feb 2019 11:04:51 GMT

you ahve to group all the field listed

another way is to

lets sat you want max of e grouped by a so do this

table:

load a,b,c,d,e from table;

left join

load a,max(e) as max_e

resident table
group by a;

Re: Data Load

khaycock — Thu, 21 Feb 2019 11:21:21 GMT

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

Left Join (Temp)
Load
PERIOD_INFO,
max(REPORT_DATE) AS MAXDATE
Resident Temp
Group by PERIOD_INFO;

Let vMaxDate = peek('MAXDATE');

Report:
NoConcatenate LOAD
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERIOD_INFO,
REPORT_DATE,
PART_NUMBER,
SOURCING_SUPPLIER,
USE_DESC_TYPE

RESIDENT Temp
WHERE REPORT_DATE = $(vMaxDate);
DROP TABLE Temp;

This is what I've put but as I drop Temp, no data is displayed.

Re: Data Load

pradosh_thakur — Thu, 21 Feb 2019 11:40:18 GMT

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


Load
PERIOD_INFO,
max(REPORT_DATE) AS MAXDATE
Resident Temp
Group by PERIOD_INFO;

Let vMaxDate = peek('MAXDATE');
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 TABLE Temp;

Re: Data Load

khaycock — Thu, 21 Feb 2019 11:43:48 GMT

That returned no data anywhere but in PERIOD_INFO

Re: Data Load

pradosh_thakur — Thu, 21 Feb 2019 11:47:42 GMT

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


Load
PERIOD_INFO,
max(REPORT_DATE) AS MAXDATE
Resident Temp
Group by PERIOD_INFO
order by REPORT_DATE ;

Let vMaxDate = peek('MAXDATE');
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 TABLE Temp;

Re: Data Load

khaycock — Thu, 21 Feb 2019 12:04:18 GMT

Still having the same issue with only PERIOD_INFO showing data

Re: Data Load

andrescc — Thu, 21 Feb 2019 13:18:42 GMT

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

Re: Data Load

khaycock — Thu, 21 Feb 2019 14:21:09 GMT

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 😞

Re: Data Load

andrescc — Thu, 21 Feb 2019 16:42:20 GMT

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

Re: Data Load

khaycock — Thu, 21 Feb 2019 16:44:35 GMT

This returned no data whatsoever 😞

Re: Data Load

andrescc — Thu, 21 Feb 2019 16:46:00 GMT

Can you upload a qvw example?

Re: Data Load

khaycock — Thu, 21 Feb 2019 16:49:13 GMT

I'm not able to attach the QVD though

Re: Data Load

andrescc — Thu, 21 Feb 2019 16:51:03 GMT

upload an extract of qvd in excel format

Re: Data Load

khaycock — Thu, 21 Feb 2019 16:55:39 GMT

Oh yeah sure!

Re: Data Load

andrescc — Thu, 21 Feb 2019 17:11:57 GMT

This qvw works!!!

Regards,

ACC