topic Re: Can you please suggest? in QlikView

Can you please suggest?

aj0031724 — Thu, 23 Apr 2015 12:48:08 GMT

I have a requirement in qlikview for my customer like this :

There is an excel which contains KPI and Forumula which we load into table as shown below

KPINAME 　　　　　　FORMULA
ERIC_DROP　　　　　A
HW_DROP 　　　　　　　B
NSN_DROP 　　　　　　C
COMBINED_DROP 　　ERIC_DROP +HW_DROP

What I need to achieve from load statement is as below:

I need to substitute the formula for COMBINED_DROP reading from KPINAME. So that final output will be :

KPINAME 　　　　　　　　　　　　　　　　FORMULA
ERIC_DROP　　　　　　　　　　　　　　A
HW_DROP　　　　　　　　　　　　　　　　　B
NSN_DROP　　　　　　　　　　　　　　　　　　C

COMBINED_DROP　　　　　　　A+B

Re: Can you please suggest?

Gysbert_Wassenaar — Thu, 23 Apr 2015 13:41:01 GMT

Load this:

LOAD * INLINE [

KPINAME, FORMULA

ERIC_DROP, A

HW_DROP, B

NSN_DROP, C

COMBINED_DROP, A

COMBINED_DROP, B

];

Then use KPINAME as dimension and sum(FORMULA) as expression.

Re: Can you please suggest?

aj0031724 — Thu, 23 Apr 2015 13:58:15 GMT

Thanks but

1)how first I split the formula of COMB_DROP = ERIC_DROP+HW_DROP DURING LOADING AND THEN SUBSTITUTE THE VALE OF (ERIC_DROP=A and HW_DROP=B while searching in entire KPINAME) to the forumula?

so that finally I could do as suggested

LOAD * INLINE [

KPINAME, FORMULA

ERIC_DROP, A

HW_DROP, B

NSN_DROP, C

COMBINED_DROP, A

COMBINED_DROP, B

];

any help would be great.

Re: Can you please suggest?

sebastiandperei — Thu, 23 Apr 2015 14:27:14 GMT

If i understood, you need that qlikview makes the search in "FORMULA" field of "KPINAME" values, so replace them in FORMULA FIELD.

Try this:

aux:

Load Distinct

KPINAME as ref_name,

FORMULA as ref_formula

Resident Table

Where len(FORMULA)=1;

let refs = NoOfRows('aux');

aux2:

Load Distinct

Repeat('Replace(',$(refs))&

'FORMULA'&

Concat(','&chr(39)&ref_name&chr(39)&','&chr(39)&ref_formula&chr(39)&')') as vReplace

Resident aux;

let vReplace=Peek('vReplace');

Left join (Table)

Load Distinct

KPINAME,

$(vReplace) as FORMULA2

Resident Table;