Crosstable help (single row into multiples)

[DMG]

Hi, i've tried searching the community and there is lots of different examples of things similar to what i'm trying to achieve, but none quite solve my issues, i'm not sure if crosstable will work and if i have the syntax correct.

I have a table loaded into qlik from excel, with a format roughly like

Person, Type, Team, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12

For example

John Doe, Normal, Ops, 1,1,1,1,1,1,1,1,1,1,1,1

Sue Doe, Temp, Ops, 0,0,0,1,1,1,1,1,0,0,0,0

I would like to create a new table, that creates a separate row of date for each person and each period, for example


Person, Type, Team, Period, Value
John Doe, Normal, Ops, 1, 1

John Doe, Normal, Ops, 2, 1

John Doe, Normal, Ops, 3, 1


Is that something i should achieve via crossjoin or another method during an extra script section of the data load ?

[Taoufiq_Zarra]

Hi,

Maye be this :

Data:

CrossTable(Period, Value, 3)

load * inline [
Person, Type, Team, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12
John Doe, Normal, Ops, 1,1,1,1,1,1,1,1,1,1,1,1
Sue Doe, Temp, Ops, 0,0,0,1,1,1,1,1,0,0,0,0
];

Final:
noconcatenate

load Person, Type, Team, keepchar(Period,'0123456789') as Period,Value resident Data;

drop table Data;

output :

[DMG]

Thanks, the output looks perfect, i'm very new to qlik however, if i wanted to take the data from an existing already loaded table (that contains many fields in addition to those needed for the crosstable) how would i alter the load inline section, to instead take certain fields for example , Full_Name (instead of Person), Type, Team, P1,...P12 from an existing table called "Resource" rather than loading the data inline.

Thanks

[Taoufiq_Zarra · Accepted solution]

if I understood correctly, you need

Data:

CrossTable(Period, Value, 3)
load Full_Name as Person, Type, Team, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12
resident Resource;

Final:
noconcatenate

load Person, Type, Team, keepchar(Period,'0123456789') as Period,Value resident Data;

drop table Data;

[Brett_Bleess]

Just adding a Design Blog post as well:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Regards,
Brett