topic Re: 2 row to 1 column scripting in QlikView

2 row to 1 column scripting

muratakkemik — Sat, 10 Nov 2018 00:08:28 GMT

Hi,

I have data like this below (in Resident table);

ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD, RN

1111 A Black L 11111 1

1111 A Black L 12121 2

1111 A White L 34343 1

1111 A White L 55555 2

2222 B Black M 66666 1

2222 B Black M 77777 2

3333 C Red S 65766 1

4444 D Red XL 34321 1

.....

I need this format (with QV script);

ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD1, EANKOD2

1111 A Black L 11111 12121

1111 A White L 34343 55555

2222 B Black M 66666 77777

3333 C Red S 65766 65766

4444 D Red XL 34321 34321

...

every ARTIKELLOPNR & ARTIKELID & EANColor & EANSize must be one line in table.

Thanks

Murat

Re: 2 row to 1 column scripting

swuehl — Tue, 13 Nov 2018 23:28:04 GMT

Maybe like this:

Result:
LOAD ARTIKELLOPNR, ARTIKELID, EANColor, EANSize,
	Only(If(RN = 1, EANKOD)) as EANKOD1,
	Alt(Only(If(RN = 2, EANKOD)),Only(If(RN = 1, EANKOD))) as EANKOD2
Resident Table 
GROUP BY ARTIKELLOPNR, ARTIKELID, EANColor, EANSize;

DROP TABLE Table;

//testing posting a reply on the new platform for the first time,

Re: 2 row to 1 column scripting

sunny_talwar — Wed, 14 Nov 2018 01:26:16 GMT

Stefan -

How did you get a box around the script?

Thanks,

Sunny

Re: 2 row to 1 column scripting

swuehl — Wed, 14 Nov 2018 09:16:51 GMT

In the rich text menu, there is a tag for code block </>.

Code block

Re: 2 row to 1 column scripting

sunny_talwar — Wed, 14 Nov 2018 12:01:44 GMT

Thanks Stefan!!

Re: 2 row to 1 column scripting

MarcoWedel — Thu, 15 Nov 2018 00:54:05 GMT

Hi,

if you don't need the redundant EANKOD2 in case there is only one row, then maybe a generic load might do as well:

table1:
Generic
LOAD ARTIKELLOPNR,
     ARTIKELID,
     EANColor,
     EANSize,
     'EANKOD'&RN,
     EANKOD
Inline [
    ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD, RN
    1111, A, Black, L, 11111, 1
    1111, A, Black, L, 12121, 2
    1111, A, White, L, 34343, 1
    1111, A, White, L, 55555, 2
    2222, B, Black, M, 66666, 1
    2222, B, Black, M, 77777, 2
    3333, C, Red, S, 65766, 1
    4444, D, Red, XL, 34321, 1
];

hope this helps

regards

Marco

Re: 2 row to 1 column scripting

Anonymous — Thu, 15 Nov 2018 19:14:16 GMT

Well, you could just load new table with distinct statement resident from this one without last field