Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I know this is an easy one, but I cannot find a lean and fast way to obtain the following, if not by using a couple of JOIN and WHERE statements.
There has to be a faster way!
Header 1 | Header 2 | Header 3 |
---|---|---|
A | A | |
B | B | |
C | M | M |
D | D | |
E | Z | Z |
I would like to have what you can see in column 3.
Basically all values of column 2, and, when cell is empty, value of column1 should be filled in!
thank you
Hi,
In a Load write script like
Temp:
Your sql load
Header1,Header2,Header3
From Source;
Data:
load *,
if(len(Header2) = 0, Header1,Header2) as Field3
Resident Temp;
DROP Table Temp;
HTH
Regards,
Anand
You can check for nulls with if(len(trim(MyFieldName))=0,..,...). If you have all the fields already in your table in becomes a simple if(len(trim(Field2))=0,Field3,Field2) to fill Field2 with the value of Field3 if there was no value in Field2.
Dear Gysbert,
thank you for your answer.
But I'm sorry, my question was not 100% clear.
The table I was showing is a SQL table and I would like to reach a solution in the script, so that I have as output Field3.
thank you!
Hi,
In a Load write script like
Temp:
Your sql load
Header1,Header2,Header3
From Source;
Data:
load *,
if(len(Header2) = 0, Header1,Header2) as Field3
Resident Temp;
DROP Table Temp;
HTH
Regards,
Anand
Hi,
Try this script
LOAD
*,
If(Len(Header2) > 0, Header2, Header1) AS Header3;
LOAD * INLINE [
Header1,Header2
A,
B,
C, M
D,
E, Z
];
Hope this helps you.
Regards,
Jagan.
Hi,
Try this example
YourSqlLoad:
LOAD * inline
[
Header1,Header2
A,
B,
C, M
D,
E, Z
];
Data:
load *,
if(len(Header2) = 0, Header1,Header2) as Header3
Resident Temp;
DROP Table Temp;
Regards,
Anand
thank you for your answers.
I think yours, Jagan, is a bit inverted (First there should be the "LOAD INLINE" and then the if statement)
but in the end what really was missing to my script is the if statement, so you deserve a recognistion too!
I; m just wondering, is using "0" [zero] a proepr way? woudln't be better to use somethign like IsNull?
thank you!