For loop and crosstable() ?

[simotrab]

Hi community!

Hi have some data whom are in this way:

load*inline

[product, from, to, label, place

a,2015,2016,alfa, x

b,2014,2017,beta, y

]

;

I need a result like this and I suppose I need a crosstable() in a for loop:

product label place  date

a           alfa   x        2015

a           alfa   x        2016

b           beta  y       2014

b           beta  y       2015

b           beta  y       2016

b           beta  y      2017

As you can see the span of the date is variable, so I'm not able to do a proper for loop mixed a crosstable I suppose.

Thanks in advance

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD product,

label,

place,

from + IterNo() - 1 as date

While from + IterNo() - 1 <= to;

LOAD * INLINE [

    product, from, to, label, place

    a, 2015, 2016, alfa, x

    b, 2014, 2017, beta, y

];

[OmarBenSalem]

years:

LOAD * INLINE [

YEAR

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

2020

];

f:

load * inline

[

product, from, to, label, place

a,2015,2016,alfa, x

b,2014,2017,beta, y

]

;

Inner Join IntervalMatch ( "YEAR" )

LOAD from, to

Resident f;

DROP fields from,to from f;

drop table years;

result:

[OmarBenSalem]

Brilliant as usual !

[simotrab]

Great, thank you very much!