topic Re: How to create calculated field from multiple tables during load in QlikView

How to create calculated field from multiple tables during load

tomdabek — Sat, 04 Nov 2017 18:15:33 GMT

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

Re: How to create calculated field from multiple tables during load

maxgro — Sat, 04 Nov 2017 18:29:40 GMT

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;

Re: How to create calculated field from multiple tables during load

tomdabek — Sat, 04 Nov 2017 19:46:10 GMT

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"

Re: How to create calculated field from multiple tables during load

el_aprendiz111 — Sat, 04 Nov 2017 20:01:19 GMT

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;

Re: How to create calculated field from multiple tables during load

maxgro — Sun, 05 Nov 2017 08:47:11 GMT

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?).

The script above doesn't crash.

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

Re: How to create calculated field from multiple tables during load

tomdabek — Mon, 06 Nov 2017 12:47:15 GMT

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,