Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have tried to assign misisng value in table with Unknown, but the script does not work.
SAP:
LOAD * INLINE
[
Group Name, Value
GA1234, 2897
GA4567, 7000
GA9831 ,1200
GA5671,9400
];
POLFA:
LOAD * INLINE
[
Group Name
GA1234,
GA4567,
GA9831
];
Group:
LOAD * INLINE [
Group
POLFA
]
;
JOIN (Group)
LOAD
[Group Name]
Resident POLFA
;
DROP Table POLFA;
LOAD
[Group Name],
if(Group = '-', 'Unknown', Group) as Group /*This does not work*/
Resident Group;
The problem is that GA9831 cannot be assigned with 'Unknown'.
Could you please give me an advice how to handle this?
Best regards,
Cornelis
Try to create the additional join with SAP for condition to work.
SAP:
LOAD * INLINE
[
Group Name, Value
GA1234, 2897
GA4567, 7000
GA9831 ,1200
GA5671,9400
];
PLFA:
LOAD * INLINE
[
Group Name
GA1234,
GA4567,
GA9831
];
Group:
LOAD * INLINE [
Group
POLFA
]
;
JOIN (Group)
LOAD
[Group Name]
Resident PLFA
;
DROP Table PLFA;
Left Join (SAP)
LOAD *
Resident Group;
DROP Table Group;
Group:
NoConcatenate
LOAD [Group Name],
Value,
if(len(trim(Group))<=0, 'Unknown', Group) as Group /*This does not work*/
Resident SAP;
DROP Table SAP;
try dis
LOAD
[Group Name],
Replace(Group,'-', 'unknown') as Group
Try to create the additional join with SAP for condition to work.
SAP:
LOAD * INLINE
[
Group Name, Value
GA1234, 2897
GA4567, 7000
GA9831 ,1200
GA5671,9400
];
PLFA:
LOAD * INLINE
[
Group Name
GA1234,
GA4567,
GA9831
];
Group:
LOAD * INLINE [
Group
POLFA
]
;
JOIN (Group)
LOAD
[Group Name]
Resident PLFA
;
DROP Table PLFA;
Left Join (SAP)
LOAD *
Resident Group;
DROP Table Group;
Group:
NoConcatenate
LOAD [Group Name],
Value,
if(len(trim(Group))<=0, 'Unknown', Group) as Group /*This does not work*/
Resident SAP;
DROP Table SAP;
Right Join (Group)
Load *
Resident SAP;
Drop table SAP;
NoConcatenate
Final:
LOAD
[Group Name],Value,
if(len(Group)<=0, 'Unknown', Group) as Group
Resident Group;
Drop table Group;
Replace above script after DROP Table POLFA;
Yes, that is the right answer.
Concatenate does not provide the solution, it must be the JOIN statement.
Thank you for your help, very useful!
Dear Chanty 4u,
Thanks you for your swift reply.
Unfortunately, it does not help but teh Replace function is something that I have not come up.
Initially, I thought ISNULL is teh solution, but it does not help, either.
Best regards,
I am glad that it was helpful
Yes, you and Kush deliver me the solution where I'm looking for.
The JOIN statement is key to handle.
Best regards,
Glad to hear that.