Copy the value into all the 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

@PrashantSangle @sunny_talwar @tresesco 

[hic · Accepted solution]

Load ID,
Number as Number1
From <Source>
Where Len(Number)=6;

Join
Load ID,
Number as Number2
From <Source>
Where Len(Number)>6;

[vikasshana]

Thanks @hic it is working now.