topic Re: Generate loading of data based on another load data file in QlikView

Generate loading of data based on another load data file

Mon, 16 Dec 2013 17:28:58 GMT

I have a file that I'm loading in as follows:

LOAD PROD_ORDER_NUMBER,

ITEM_NUMBER,

COMPANY,

OPERATION,

SEQ_NO,

WORK_CENTER,

TASK,

MAN_HRS,

MACHINE_HRS,

LABOR_COSTS,

OVERHEAD_COSTS,

TRANSACTION_DATE,

OPERATION_RATE_CODE,

COST_PRICE_COMP_OH,

COST_PRICE_COMP_LABOR;

SQL SELECT *

from AD_PROD_OP_HRS;

I have another file called AD_COST_HRS that contains some additional information. The file contains the same PROD_ORDER_NUMBER as the file listed

and it also contains a field called cost_price_comp.

The cost_price_comp in AD_COST_HRS could contain a value equal to cost_price_comp_OH or equal to cost_price_comp_labor.

How can I load in records from the AD_COST_HRS if the cost_price_comp is equal to cost_price_comp_oh or cost_price_comp_labor???

Re: Generate loading of data based on another load data file

Gysbert_Wassenaar — Mon, 16 Dec 2013 17:46:24 GMT

You'll have to create table that contains all three fields needed for the comparison first. Then retrieve the productnumbers that match your conditions. Finally use this list to restrict the records retrieved from ad_cost_hrs. Something like this:

Temp1:
Load PROD_ORDER_NUMBER,
    COST_PRICE_COMP_OH,
    COST_PRICE_COMP_LABOR
resident AD_PROD_OP_HRS;

join (Temp1)
Load PROD_ORDER_NUMBER, COST_PRICE_COMP
from AD_COST_HRS;

Temp2:
load PROD_ORDER_NUMBER as PNum
resident Temp1
where COST_PRICE_COMP = COST_PRICE_COMP_OH or COST_PRICE_COMP = COST_PRICE_COMP_LABOR;

AD_COST_HRS:
Load * from AD_COST_HRS 
where exists(PNum, PROD_ORDER_NUMBER);

drop tables Temp1, Temp2;

Re: Generate loading of data based on another load data file

Mon, 16 Dec 2013 22:12:37 GMT

for the ad_cost_hrs:

not sure if I understand the last part, how does it tie to temp2????

Re: Generate loading of data based on another load data file

Mon, 16 Dec 2013 22:14:34 GMT

also the prod_order_number might be listed multiple times so would this still work???