Creating a list from an excel mapping

[Oyandresen]

Hi,

I'm trying to add an excel mapping to my Qlikview report, to link Cost Centers to mapped item.

The excel spreadsheet looks like this:

The results I'm aiming for, would be a list showing:   

Cost Center	Mapping
11	A23-B25-D22
12	A23-B25-D22
13	B27
17	D45
25	A23-B25-D22

or:

Cost Center	Mapping
11	A23
11	B25
11	D22
12	A23
12	B25
12	D22
13	B27
17	D45
D25	A23

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
CrossTable(Map,Value)
LOAD * INLINE [
    Cost Center, A23, B25, B27, D45, D22
    11, x, x, , , x
    12, x, x, , , x
    13, , , x, , 
    17, , , , x, 
    25, x, x, , , x
];

tab2:
NoConcatenate
LOAD [Cost Center], Concat(DISTINCT Map,'-') As Mapping
Resident tab1
Where Value='x'
Group By [Cost Center];

Drop Table tab1;

[Saravanan_Desingh]

Output:

[Saravanan_Desingh · Accepted solution]

One more version,

tab1:
CrossTable(Map,Value)
LOAD * INLINE [
    Cost Center, A23, B25, B27, D45, D22
    11, x, x, , , x
    12, x, x, , , x
    13, , , x, , 
    17, , , , x, 
    25, x, x, , , x
];

tab2:
NoConcatenate
LOAD [Cost Center], Map As Mapping
Resident tab1
Where Value='x';

Drop Table tab1;

[Saravanan_Desingh]

Output:

[Oyandresen]

Thanks for the swift response!

I will try that 🙂

[Oyandresen]

This solved my issue!

Thanks again!