How to create calculated field from multiple tables during load

[tomdabek]

Hello,

I have inherited an application that contains these 2 tables:

FACTS and FACT_FEES

I want to create a calculated field in FACTS to make expressions less complicated.

I have:

FACTS with fields

bill

revenue

rebate

FACT_FEES with fields

bill

fee1

fee2

I would like to add some script that at end of load updates table FACTS to create additional field called totalrev

where totalrev = revenue + rebate + fee1 + fee2

How can one accomplish this?

Thanks

[maxgro]

if bill is unique (no duplicate)

load FACTS .

load FACT_FEES

// add fee1 and fee2 to FACTS

left join (FACTS)

load

     bill,

     fee1, fee2

resident FACT_FEES;

// calculate totalrev

left join (FACTS)

load

     bill,

     revenue + rebate + fee1 + fee2 as totalrev

resident FACTS;

drop table FACT_FEES;

[tomdabek]

I think bill is theoretically supposed to be unique, but it is not.

How does this impact the script?

I cannot even test because Qlikview crashes when i try.

There is an error message that the application is out of memory when i try the proposed solution, the file is only 200MB

"OUT OF VIRTUAL AND/OR LOGICAL MEMORY Allocating 2MB"

[el_aprendiz111]

Hi Tom

1 exm:

FACTS:
LOAD * Inline
[
bill,revenue,rebate,
1,5,3,
2,6,5,
3,9,4,
4,8,9,
5,11,3,
];

Left Join

FACTS_FEETS:
LOAD * Inline
[
bill,fee1,fee2,
3,11,25,
2,25,36,
1,36,45,
];

NoConcatenate

SUMARY:
LOAD *, RangeSum(revenue,rebate,fee1,fee2) AS totalrev Resident FACTS;

DROP Table FACTS;

[maxgro · Accepted solution]

1)

If you have a bill not unique in FACTS and the same bill not unique in  FACT_FEES how do you link the biil in FACT_FEES with bill in FACTS?

Using Fer Fer (thanks) data; I added a duplcaited bill 1

FACTS:

LOAD * Inline

[

bill,revenue,rebate,

1,5,3,

1,1,1,

2,6,5,

3,9,4,

];

Left Join

//FACTS_FEETS:

LOAD * Inline

[

bill,fee1,fee2,

3,11,25,

2,25,36,

1,36,45,

1,2,2,

];

SUMARY:

NoConcatenate

LOAD *, RangeSum(revenue,rebate,fee1,fee2) AS totalrev Resident FACTS;

DROP Tables FACTS;

Bill 1 is duplicated, the result isn't correct

So if you have duplicated bill , you should solve this before. Maybe with a group by if the duplicated bill are the same, maybe adding some other fields (date of the bill?).

2)

The script above doesn't crash.

At the end of the script I removed (drop) the FACTS table, SUMARY is the new FACTS table.

[tomdabek]

Thank you Massimo,

Yes, i do not know how to solve this issue with the non unique bills.

There are some odd circumstances where the bill number is repeated but so far it does not have adverse effects,