Can you please suggest?

[aj0031724]

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

[Gysbert_Wassenaar]

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.

[aj0031724]

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.

[sebastiandperei]

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;