Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have a following data and having a brain lag with resolving the issue, would appreciate any help (sorry if the Subject name is misleading)
The initial data looks like the following
FieldA, FieldB
01.01.1999
a, 10
b, 20
02.02.1999
c, 11
b, 21
The desirable outcome is
FieldA, FieldB, DateField
a, 10, 01.01.1999
b, 20, 01.01.1999
c, 11, 02.02.1999
b, 21, 02.02.1999
What would be the easiest way to transform the table that way?
Thank you in advance
May be like this
Data:
Load * Inline [
FieldA, FieldB
01.01.1999
a, 10
b, 20
02.02.1999
c, 11
b, 21];
Data_Temp:
Load FieldA, FieldB, if(Len(DateField)<1,Peek(DateField),DateField)as DateField ;
Load FieldA, FieldB, if(len(FieldA)>9,FieldA) as DateField resident Data;
Drop Table Data;
Load FieldA, FieldB,DateField ,RecNo()Resident Data_Temp where len(FieldA)<9;
Drop Table Data_Temp;
Regard
Raman
Hi Raman,
Thanks for the reply,
I actually figured out the following option:
Data_tmp: Load *,
if(FieldA like '?*.?*.????', FieldA, peek(DateField)) as DateField
Inline [ FieldA, FieldB 01.01.1999 a, 10 b, 20 02.02.1999 c, 11 b, 21];
And then just cleaning the table
Data: NoConcatenate Load * Resident Data_tmp Where FieldA <> '?*.?*.????'; drop table Data_tmp;