Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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;
Output:
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;
Output:
Thanks for the swift response!
I will try that 🙂
This solved my issue!
Thanks again!