Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
c_pannese
Partner - Creator
Partner - Creator

transpose a table, rows and columns.

Hello,

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

My table has this aspect:

DATEJEN
FEBMARAPRMAYJUNJULAUGSEP
#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.

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

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 ?

View solution in original post

10 Replies
YoussefBelloum
Champion
Champion

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

Capture.PNG

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:

Capture.PNG

c_pannese
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

This is what i have after the Crosstable statement.

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

Can you help me?

YoussefBelloum
Champion
Champion

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

c_pannese
Partner - Creator
Partner - Creator
Author

this is my expectation.

YoussefBelloum
Champion
Champion

PFA

c_pannese
Partner - Creator
Partner - Creator
Author

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
Champion
Champion

You're welcome !

good luck