Data Load

[khaycock]

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.

[pradosh_thakur]

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

[khaycock]

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

[pradosh_thakur]

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

[khaycock]

Does it matter which fields I group?

[pradosh_thakur]

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;

[khaycock]

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]

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;

[khaycock]

That returned no data anywhere but in PERIOD_INFO

[pradosh_thakur]

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;