Tranpose table - rows to column & column to rows

[pratham39]

Hi , 

I have below table

I want to convert this table into below format,

Can somebody help?

Thanks

[BrunPierre]

Hi , as below.

Data:
LOAD City as Designation,
Mumbia,
Banglore,
Delhi,
Pune

FROM SourceTable,
filters(
Transpose()
));

[neerajthakur]

You can use Crosstable function.

Refer this example: 

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...)

[pratham39]

Hi 

thanks for input

Cross table wont work , please see my requirement clearly ,

with crosstable I won't get desire results which i m expecting 

Thanks

[neerajthakur]

tmpData:
Crosstable (Designation, Values)
Load * inline [
City, L1, L2, L3, L4, L5, L6
Mumbai, 1, 0, 0, 1, 1, 0
Bangalore, 0, 0, 1, 1, 1, 0
Delhi, 1, 1, 0, 0, 0, 0
Pune, 1, 0, 0, 1, 1, 1
];

Final:
Load Designation,
City as City2,
Values

Resident tmpData;

Drop Table tmpData;

Exit Script;

After crosstable you can use Pivot Table to show data like this.

[anat]

T:
CrossTable(city1,sales)
Load * Inline
[
city,L1,L2,L3
Mumbai,1,2,3
Delhi,2,3,4
Bgl,4,5,6

];

EXIT SCRIPT;

in front end use the pivot table to get desired results.

[pratham39]

Hi everyone ,

Thanks for your all inputs

I Don't want my expected output on frontend in pivot table,

Cant that be possible on backend only 

I want my expected table in backend only

Thanks

[neerajthakur]

tmpData:
Crosstable (Designation, Values)
Load * inline [
City, L1, L2, L3, L4, L5, L6
Mumbai, 1, 0, 0, 1, 1, 0
Bangalore, 0, 0, 1, 1, 1, 0
Delhi, 1, 1, 0, 0, 0, 0
Pune, 1, 0, 0, 1, 1, 1
];

Generic
Final:
Load
Designation,
City as City2,
Values

Resident tmpData;

Drop Table tmpData;

Exit Script;

[MayilVahanan · Accepted solution]

Hi

Try like below

CrossTable(Designation, Value)
Temp:
Load * Inline
[
City, L1, L2, L3, L4, L5
Bangalore, 1, 0, 0, 1, 1
Delhi, 1, 1, 1, 0, 0
Chennai, 0, 0, 0, 1, 1
];

GenericTemp:
Generic Load Designation, City, Value Resident Temp;

LevelJoin:
Load Distinct Designation Resident Temp;

FOR i = NoOfTables()-1 to 0 STEP -1;
LET vTable=TableName($(i));
IF WildMatch('$(vTable)','GenericTemp.*') THEN
LEFT JOIN ([LevelJoin]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i

Its similar to @neerajthakur , with merge all the generic table to single table. 

[pratham39]

Thanks @MayilVahanan !!