transpose a table, rows and columns.

[c_pannese]

Hello,

i'd like to transpose the structure of an excel table.

My table has this aspect:

	DATE	JEN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP
#MEASURE1
#MEASURE2
#MEASURE3
#MEASURE4
#MEASURE5
#MEASURE6

and i'm trying to realize somthing like this

DATE	#MEASURE1	#MEASURE2	#MEASURE3	#MEASURE4	#MEASURE5	#MEASURE6
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP

Now, i tought to use crosstable  statement first and elaborate my new table starting from this discussion:

cross table in Qlik Sense

it seems work but not correctly because the same row is repeted many time instead of only one.

i expect to see only one row for month.

Have any  suggestion?

Thanks a lot.

[YoussefBelloum · Accepted solution]

Hi,

it is correct that with Crosstable you can transpore your columns.

But, to have only one row for january for example after the crosstable, yo should have only one value filled on all the measures for january, otherwise Jan row(s) will be equal the value(s) filled on the column january before the transpose.

Can you attach a sample app or sample data ?

[OmarBenSalem]

Maybe there is a better approach (with generic load)

But for the time being, I was able to do this as follow:

the original table

When u load the table; load only from the Date field (don't load the Measures..)

and do as follow:

table:

CrossTable(Month,value)

LOAD

    "Date",

    "1",

    "2",

    "3",

    "4",

    "5",

    "6",

    "7",

    "8",

    "9",

    "10",

    "11",

    "12"

FROM [lib://Mariage/test.xlsx]

(ooxml, embedded labels, table is Sheet1);

drop field Date;

final0:

load * ,RowNo(total) as row Resident table;

Drop Table table;

Final:

load Month, value as Measure1 Resident final0 where row<=12;

left Join(Final)

load Month, value as Measure2 Resident final0 where row>12 and row<=24;

left Join(Final)

load Month, value as Measure3 Resident final0 where row>24;

drop Field row;

drop Table final0;

Result:

[c_pannese]

i'm trying to do what you suggest, but i have many measures for 3 years(so 36 months) .

should i use a variable ?

my script is:

Crosstable:

CrossTable("year-month",measure)

LOAD 

measure As NameMeasure,

   "201601",

   "201602",

   "201603",

   "201604",

   "201605",

   "201606",

   "201607",

   "201608",

   "201609",

   "201610",

   "201611",

   "201612",

     (etc...)

FROM [lib://TEST_EXCEL/Export mensile_20175.xlsx]

(ooxml, embedded labels, table is Foglio2);

Table:

load

"year-month",

NameMeasure,

measure,

RowNo(TOTAL) as Rowno

Resident Crosstable

order by "year-month";

Drop Table Crosstable;

FinalTable:

LOAD DISTINCT "year-month"

Resident Table;

for j=1 to 12

For i = 1 to FieldValueCount('NameMeasure')

  LET vField = FieldValue('NameMeasure', $(i));

    Left Join (FinalTable)

    LOAD distinct  "year-month",

    measure as [$(vField)]

    Resident Table

    Where Rowno>FieldValueCount('NameMeasure')*($(j)-1) and

Rowno<= FieldValueCount('NameMeasure')*$(j);

next

Next

DROP Table Table;

[c_pannese]

This is what i have after the Crosstable statement.

Now the rows of the column "NameMeasure" should become columns.

Can you help me?

[YoussefBelloum]

Can you attach the same table sctructure as the one attached above, but with data on the months columns this time ?

[c_pannese]

this is my expectation.

[YoussefBelloum]

PFA

[c_pannese]

Yousself,

i have used crosstable but it's not enought. Anyway i've just solved using Crosstable and

The Generic Load

It works.

Tahnk you very much for your advice

[YoussefBelloum]

You're welcome !

good luck