Cross Table

[thannila]

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;

can anyone explain this code?

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)';

My doubt is how we are getting the sales value here?

[olivierrobin · Accepted solution]

hello

for i=1 to number of values of field month

vField=value of ith occurence of month

left join existing table and adding new column named with the month name and containing the value of the month

at the end, on each row, you have 5 more columns named Jan 2017, ... Jun 2017

[thannila]

But how we are getting the sales value???

[olivierrobin]

here

Sales as [$(vField)]

the field Sales is left joined under the name contained in the field vField

[thannila]

Thank you for your explanation.But instead of using this we can use generic load.

[olivierrobin]

generic load will create 1 table per month value

all depends on your needs