Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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;
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
thank you