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: 
beck_bakytbek
Master
Master

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
sunny_talwar

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
sunny_talwar

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

beck_bakytbek
Master
Master
Author

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

micheledenardi
Specialist II
Specialist II

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
beck_bakytbek
Master
Master
Author

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

sunny_talwar

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;

micheledenardi
Specialist II
Specialist II

For qliksense is the same.

You can do that with more than one dimension.

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
beck_bakytbek
Master
Master
Author

Sunny, thanks a lot for your help,

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

Thanks a lot

Beck

rydr2102
Contributor
Contributor

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