Copy the value into next rows

[vikasshana]

Hi,

I've the below data in my app.

ID	Number
1	264569
1	264896789563
1	647896321456
1	456789123456
1	234567896542
2	756324
2	596478963215
2	236974596321

And using the below load script to create two new columns like below

Load ID,
Number,
if(Len(Number)=6, Number) as Number1
if(Len(Number)>6, Number) as Number2

I received below output.

ID	Number	Number1	Number2
1	264569	264569
1	264896789563		264896789563
1	647896321456		647896321456
1	456789123456		456789123456
1	234567896542		234567896542
2	756324	756324
2	596478963215		596478963215
2	236974596321		236974596321

But I'm looking for below output

ID	Number	Number1	Number2
1	264896789563	264569	264896789563
1	647896321456	264569	647896321456
1	456789123456	264569	456789123456
1	234567896542	264569	234567896542
2	596478963215	756324	596478963215
2	236974596321	756324	236974596321

@tresesco 

[vikasshana]

Hi,

I've the below data in my app.

ID	Number
1	264569
1	264896789563
1	647896321456
1	456789123456
1	234567896542
2	756324
2	596478963215
2	236974596321

And using the below load script to create two new columns like below

Load ID,
Number,
if(Len(Number)=6, Number) as Number1
if(Len(Number)>6, Number) as Number2

I received below output.

ID	Number	Number1	Number2
1	264569	264569
1	264896789563		264896789563
1	647896321456		647896321456
1	456789123456		456789123456
1	234567896542		234567896542
2	756324	756324
2	596478963215		596478963215
2	236974596321		236974596321

But I'm looking for below output

ID	Number	Number1	Number2
1	264896789563	264569	264896789563
1	647896321456	264569	647896321456
1	456789123456	264569	456789123456
1	234567896542	264569	234567896542
2	596478963215	756324	596478963215
2	236974596321	756324	236974596321

@tresesco 

[brunobertels]

hi 

may be this 

if(Len(Number)=6, Number, peek(Number1)) as Number1
if(Len(Number)>6, Number, peek(Number2)) as Number2

[vikasshana]

I tried it but still the same

[tresesco · Accepted solution]

Try like:

t1:
Load * Inline [
ID, Number
1, 264569
1, 264896789563
1, 647896321456
1, 456789123456
1, 234567896542
2, 756324
2, 596478963215
2, 236974596321];

NoConcatenate
t2:
Load ID,
Number as Number1 Resident t1 Where len(Number)=6;
Join
Load ID,
Number as Number2 Resident t1 Where Len(Number)>6;

DROP Table t1;

[vikasshana]

Thanks @tresesco it is working now.