Solved: Data Load - Qlik Community

khaycock · ‎2019-02-21

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.

andrescc · ‎2019-02-25

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

View solution in original post

pradosh_thakur · ‎2019-02-21

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

Learning never stops.

khaycock · ‎2019-02-21

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

pradosh_thakur · ‎2019-02-21

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

Learning never stops.

khaycock · ‎2019-02-21

Does it matter which fields I group?

pradosh_thakur · ‎2019-02-21

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;

Learning never stops.

khaycock · ‎2019-02-21

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.

pradosh_thakur · ‎2019-02-21

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;

Learning never stops.

khaycock · ‎2019-02-21

That returned no data anywhere but in PERIOD_INFO

pradosh_thakur · ‎2019-02-21

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;

Learning never stops.

Data Load

data

Related Topics