Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
beck_bakytbek
Honored Contributor

cross table in Qlik Sense

Hi Folks,

my situation does look like: i have this table:

Produc, Month, Sales

A, Jan 2017, 10

A, Mar 2017, 20

A, Apr 2017, 30

B, Mai 2017, 40

B, Jun 2017, 50

i want to convert this table into following table:

Product, Jan 2017, Mar 2017, Apr 2017, Mai 2017, Jun 2017

A,          10               20          30                0           0

B            0                  0           0               40          50

Does have anybody any idea?

Thanks a lot for your feedback and help

Beck

1 Solution

Accepted Solutions
Highlighted

Re: cross table in Qlik Sense

May be this

Table:

LOAD * INLINE [

Product, Month, Sales

A, Jan 2017, 10

A, Mar 2017, 20

A, Apr 2017, 30

B, Mai 2017, 40

B, Jun 2017, 50

];

FinalTable:

LOAD DISTINCT Product

Resident Table;

For i = 1 to FieldValueCount('Month')

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

   

    Left Join (FinalTable)

    LOAD DISTINCT Product,

    Sales as [$(vField)]

    Resident Table

    Where Month = '$(vField)';

   

Next

DROP Table Table;

View solution in original post

8 Replies
Highlighted

Re: cross table in Qlik Sense

You are looking for The Generic Load and not CrossTable load here.

Highlighted
beck_bakytbek
Honored Contributor

Re: cross table in Qlik Sense

Hi Sunny, thanks a lot for your help and feedback

i tried the code from this issue, but i can.t solve it, do you have any alternative ideas?

Thanks a lot

Beck

Highlighted
micheledenardi
Valued Contributor

Re: cross table in Qlik Sense

Can I suggest to symply create a pivot table and drag and drop "Month" dimension above the header of the Measure sum(sales) ?

Immagine.png

Highlighted
beck_bakytbek
Honored Contributor

Re: cross table in Qlik Sense

Hi Michele,

First of all, thanks a lot for help and idea, but what shoul i do, if i should use a few dimension? and dont forget i am using Qlik Sense not QlikView

Thanks a lot

Beck

Highlighted

Re: cross table in Qlik Sense

May be this

Table:

LOAD * INLINE [

Product, Month, Sales

A, Jan 2017, 10

A, Mar 2017, 20

A, Apr 2017, 30

B, Mai 2017, 40

B, Jun 2017, 50

];

FinalTable:

LOAD DISTINCT Product

Resident Table;

For i = 1 to FieldValueCount('Month')

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

   

    Left Join (FinalTable)

    LOAD DISTINCT Product,

    Sales as [$(vField)]

    Resident Table

    Where Month = '$(vField)';

   

Next

DROP Table Table;

View solution in original post

Highlighted
micheledenardi
Valued Contributor

Re: cross table in Qlik Sense

For qliksense is the same.

You can do that with more than one dimension.

Highlighted
beck_bakytbek
Honored Contributor

Re: cross table in Qlik Sense

Sunny, thanks a lot for your help,

i overlooked a variable in code, now i solved my problem

Thanks a lot

Beck

Highlighted
rydr2102
New Contributor

Re: cross table in Qlik Sense

Sunny - This script you posted bailed me out so thanks!