topic Listing values horizontally in QlikView

Listing values horizontally

selvakumarsr — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Listing values horizontally

gayandilhara — Sun, 06 May 2018 08:45:07 GMT

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.

Re: Listing values horizontally

Anil_Babu_Samineni — Sun, 06 May 2018 09:32:36 GMT

Can i know the reason why you want to split only 3 columns from single Column? May be explain

Re: Listing values horizontally

selvakumarsr — Sun, 06 May 2018 09:36:34 GMT

Hi Anil,

It will be multiple (n number) of columns. For example i entered 3.

Thanks,

Selva

Re: Listing values horizontally

vinieme12 — Sun, 06 May 2018 10:12:07 GMT

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;

Re: Listing values horizontally

vinieme12 — Sun, 06 May 2018 10:15:34 GMT

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