Multiplicate specific entries in a table

[jleberre]

Hello

I would like to transform this table:

customer, rdv, rdv month

A, 1, march

B, 0 , -

C, 1, july

into this table:

customer, month

A, january

A, february

A, march

A, april

A, may

C, may

C, june

C, july

C, august

C, september

ie one entry in the first table where "rdv"=1, should be transformed into 5 entries in the new table (2 months before, 2 months after the month rdv)

Does anyone have an idea?

Thanks!

[jleberre]

Note that I've already found this solution, but I think it could be optimized:

rdvtable:

NoConcatenate

load * inline [

customer,rdv,rdvmonth

A,1,15/03/2017

B,0,

C,1,10/07/2017

];

newtable:

NoConcatenate

load Distinct customer, monthname(rdvmonth) as month

Resident rdvtable

where rdv='1';

Concatenate

load Distinct customer, monthname(AddMonths(rdvmonth,-2)) as month

Resident rdvtable

where rdv='1';

Concatenate

load Distinct customer, monthname(AddMonths(rdvmonth,-1)) as month

Resident rdvtable

where rdv='1';

Concatenate

load Distinct customer, monthname(AddMonths(rdvmonth,1)) as month

Resident rdvtable

where rdv='1';

Concatenate

load Distinct customer, monthname(AddMonths(rdvmonth,2)) as month

Resident rdvtable

where rdv='1';

drop tables rdvtable;

[kenphamvn · Accepted solution]

Hi

Just modify some code

rdvtable:
NoConcatenate
load * inline [
customer,rdv,rdvmonth
A,1,15/03/2017
B,0,
C,1,10/07/2017
];

newtable:
NoConcatenate

load Distinct customer, monthname(rdvmonth) as month
Resident rdvtable
where rdv='1';

for each v_num in -2,-1,1,2 
Concatenate
load Distinct customer, monthname(AddMonths(rdvmonth,$(v_num ))) as month
Resident rdvtable
where rdv='1';
next

drop tables rdvtable;

regards

An Pham

[jleberre]

thank you