Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
c_pannese
New Contributor III

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
Esteemed Contributor

Re: transpose a table, rows and columns.

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 ?

9 Replies
YoussefBelloum
Esteemed Contributor

Re: transpose a table, rows and columns.

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
Esteemed Contributor

Re: transpose a table, rows and columns.

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
New Contributor III

Re: transpose a table, rows and columns.

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
New Contributor III

Re: transpose a table, rows and columns.

This is what i have after the Crosstable statement.

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

Can you help me?

YoussefBelloum
Esteemed Contributor

Re: transpose a table, rows and columns.

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

c_pannese
New Contributor III

Re: transpose a table, rows and columns.

this is my expectation.

YoussefBelloum
Esteemed Contributor

Re: transpose a table, rows and columns.

PFA

c_pannese
New Contributor III

Re: transpose a table, rows and columns.

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
Esteemed Contributor

Re: transpose a table, rows and columns.

You're welcome !

good luck