Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to list values in Pivot or Straight Table Horizontally. Is it possible?
I have tried cross table transformation but i'm not getting the expected output
Input Table:
SID | PL |
---|---|
S001 | 123 |
S001 | 456 |
S002 | 345 |
S002 | 888 |
S002 | 777 |
Expected Output:
SID | PL | PL | PL | |
---|---|---|---|---|
S001 | 123 | 456 | - | |
S002 | 345 | 888 | 777 |
Thanks,
Selva
OR if you want to show just in front end then this is enough
temp:
LOAD SID,
PL,
'PL'&AutoNumber(PL,SID) as InstanceName,
AutoNumber(PL,SID) as InstanceNum
FROM
[https://community.qlik.com/thread/300412]
(html, codepage is 1252, embedded labels, table is @1);
then use a Pivot Chart with SID and InstanceName as Dimension
Did you manage to get the table 1 to appear on QV? Normally I would expect the PL values to to aggregated for each SID eg: S001 -> 123 + 456. Maybe would help if you post the data model?
However in the expected output, you might be able to get the details as specified if you have a specific criteria for each column eg: PL1 = sum of all SID where condition = 1 etc.
Can i know the reason why you want to split only 3 columns from single Column? May be explain
Hi Anil,
It will be multiple (n number) of columns. For example i entered 3.
Thanks,
Selva
If you want to do this in script then use below
temp:
LOAD SID,
PL,
AutoNumber(PL,SID) as Instance
FROM
[https://community.qlik.com/thread/300412]
(html, codepage is 1252, embedded labels, table is @1);
t2:
load max(Instance) as NoOfCol
Resident temp;
let vNoOfCols = Peek('NoOfCol');
for i = 1 to $(vNoOfCols)
let vFieldName = 'PL'&$(i);
if $(i)=1 THEN
FACT:
LOAD DISTINCT SID
Resident temp;
Left Join(FACT)
LOAD SID
,PL as $(vFieldName)
Resident temp
Where Instance =$(i);
ELSE
Left Join(FACT)
LOAD SID
,PL as $(vFieldName)
Resident temp
Where Instance =$(i);
End if
Next i;
Drop table temp,t2;
OR if you want to show just in front end then this is enough
temp:
LOAD SID,
PL,
'PL'&AutoNumber(PL,SID) as InstanceName,
AutoNumber(PL,SID) as InstanceNum
FROM
[https://community.qlik.com/thread/300412]
(html, codepage is 1252, embedded labels, table is @1);
then use a Pivot Chart with SID and InstanceName as Dimension