topic Re: script columns to column in QlikView

script columns to column

Wed, 11 Feb 2015 09:35:25 GMT

Hi All,

How can i do the following in the script:

Input:

Data | Group1 | Group2 | Group3

1 A B C

2 A D C

Output:

Data | Group

1 A

1 B

1 C

2 A

2 D

2 C

Thanks in advance.

Re: script column field to one column

simondachstr — Wed, 11 Feb 2015 09:39:35 GMT

Use the CrossTable function preceding your LOAD statement in your script, e.g.

Crosstable(Data, Group,1)

LOAD

Data,

Group1,

Group2,

Group3

Resident Input;

Re: script columns to column

alexandros17 — Wed, 11 Feb 2015 09:44:58 GMT

This is the code:

let me know

Input:
LOAD * Inline [
Data , Group1 , Group2 , Group3
1, A, B, C
2, A, D, C
];

MyTable1: NoConcatenate
LOAD Data Resident Input;
Left Join
LOAD Data, Group1 as Group Resident Input;

MyTable2: NoConcatenate
LOAD Data Resident Input;
Left Join
LOAD Data, Group2 as Group Resident Input;

MyTable3: NoConcatenate
LOAD Data Resident Input;
Left Join
LOAD Data, Group3 as Group Resident Input;

DROP Table Input;

MyTable:
NoConcatenate
LOAD * Resident MyTable1;
Concatenate
LOAD * Resident MyTable2;
Concatenate
LOAD * Resident MyTable3;

DROP Tables MyTable1, MyTable2, MyTable3;

Re: script columns to column

simondachstr — Wed, 11 Feb 2015 09:52:45 GMT

Input:

LOAD * Inline [
Data , Group1 , Group2 , Group3
1, A, B, C
2, A, D, C
];

Final:
Crosstable(Data1, Group,1)
LOAD
Data,
Group1,
Group2,
Group3
Resident Input;

Drop fields Data1 from Final;

Drop Tables Input;

Re: script columns to column

alexandros17 — Wed, 11 Feb 2015 09:56:29 GMT

Crosstable is the best Solution I know but my example is a useful exercise to manage tables!

Re: script columns to column

PrashantSangle — Wed, 11 Feb 2015 10:01:35 GMT

Hi,

One more solution

TEMP:
LOAD * INLINE [
Data, Group1, Group2, Group3
1, A, B, C
2, A, D, C
];

FINAL:
LOAD Data,
Group1 as Group
Resident TEMP;

join
LOAD Data,
Group2 as Group
Resident TEMP;

join
LOAD Data,
Group3 as Group
Resident TEMP;

DROP table TEMP;

Regards