Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Load ID,
Number as Number1
From <Source>
Where Len(Number)=6;
Join
Load ID,
Number as Number2
From <Source>
Where Len(Number)>6;
Load ID,
Number as Number1
From <Source>
Where Len(Number)=6;
Join
Load ID,
Number as Number2
From <Source>
Where Len(Number)>6;
Thanks @hic it is working now.