topic Re: Creating a list from an excel mapping in QlikView

Creating a list from an excel mapping

Oyandresen — Fri, 12 Mar 2021 13:40:35 GMT

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

Re: Creating a list from an excel mapping

Saravanan_Desingh — Fri, 12 Mar 2021 13:51:56 GMT

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;

Re: Creating a list from an excel mapping

Saravanan_Desingh — Fri, 12 Mar 2021 13:52:18 GMT

Output:

Re: Creating a list from an excel mapping

Saravanan_Desingh — Fri, 12 Mar 2021 13:54:36 GMT

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;

Re: Creating a list from an excel mapping

Saravanan_Desingh — Fri, 12 Mar 2021 13:54:59 GMT

Output:

Re: Creating a list from an excel mapping

Oyandresen — Fri, 12 Mar 2021 14:07:01 GMT

Thanks for the swift response!

I will try that 🙂

Re: Creating a list from an excel mapping

Oyandresen — Fri, 12 Mar 2021 14:19:23 GMT

This solved my issue!

Thanks again!