cross table in Qlik Sense

[beck_bakytbek]

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

[sunny_talwar]

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

[beck_bakytbek]

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]

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

[beck_bakytbek]

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 · Accepted solution]

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]

For qliksense is the same.

You can do that with more than one dimension.

[beck_bakytbek]

Sunny, thanks a lot for your help,

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

Thanks a lot

Beck

[rydr2102]

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

[Sergej_Komar]

Hi Sunny, 

many thanks for your help, I found this solution very helpful when in comes to table "pivoting". 

Could you please help me with the next step related to this sript. After I completed loop, I need to set all cell where there is no value to "0". Could you please help me we that? What would be a script. 

There is a part of my sript : 

[Final_FC_temp]:

Load distinct 

PROD_HIER_ID,

    CUSTOMER_GROUP,

    MONTH_YEAR_FC

Resident [FC_group];

For i=1 to FieldValueCount('TIME_LAG')

let vField=FieldValue('TIME_LAG',$(i)); 

    Left Join (Final_FC_temp)

    Load distinct

    PROD_HIER_ID,

            CUSTOMER_GROUP,

            MONTH_YEAR_FC,

            SUM_FC_QTY as [$(vField)]

            Resident FC_group

            Where TIME_LAG='$(vField)';

    Next

Drop table [FC_group];

join (Final_FC_temp)

Load

[MONTH_YEAR_CO] as MONTH_YEAR_FC

Resident [Master_Calendar_Month];

join (Final_FC_temp)

Load     

MONTH_YEAR_CO as MONTH_YEAR_FC, 

    [%PROD_HR] as PROD_HIER_ID,

    CUST_GROUP as CUSTOMER_GROUP,

    SUM_ACT_QTY

Resident [Orders];

Drop table [Orders];

THAT IS WHERE I STRUGGLE

// [Full_table]:

// NoConcatenate load

// PROD_HIER_ID,

//     CUSTOMER_GROUP,

//     MONTH_YEAR_FC,

//    IF(ISNULL('$(vField)'),0,'$(vField)') as [$(vField)],

//     IF(ISNULL(SUM_ACT_QTY),0,SUM_ACT_QTY) as SUM_ACT_QTY

// Resident [Final_FC_temp];

// Drop table [Final_FC_temp];

Many thanks for your help 

Best regards