Missing value in table script

[#1]

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

[Chanty4u]

try dis

LOAD

[Group Name],

Replace(Group,'-', 'unknown') as Group

[Kushal_Chawda · Accepted solution]

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;

[Digvijay_Singh]

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;

[Digvijay_Singh]

Replace above script after DROP Table POLFA;

[#6]

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!

[#7]

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,

[Kushal_Chawda]

I am glad that it was helpful

[#9]

Yes, you and Kush deliver me the solution where I'm looking for.

The JOIN statement is key to handle.

Best regards,

[Digvijay_Singh]

Glad to hear that.