Setting a default value on a column within a load statement

[kschramm]

Hi.  I want to be able to set a default to the "Partner" column if it is null in the attributes QVD.  How is that accomplished within the load statements?  If it's blank or null, I want to populate it with "Other".  Thank you in advance. 

Left Join

LOAD
HPROP  as HMY,
SUBGROUP9  as Partner
FROM   $(vPathQVD)Attributes.qvd (qvd);

[brenner_martina]

Hallo Kurt,

you can use the following Statement:

Left Join
LOAD
HPROP  as HMY,
If(SUBGROUP9=' ' Or IsNull(SUBGROUP9 ), 'Other', SUBGROUP9)  as Partner
FROM   $(vPathQVD)Attributes.qvd (qvd);

Greetings from Martina Brenner

EVACO GmbH, Munich

[sunny_talwar · Accepted solution]

May be this:

Left Join (TableName)
LOAD
HPROP  as HMY,
If(Len(Trim(SUBGROUP9)) = 0, 'Other', SUBGROUP9)  as Partner
FROM  $(vPathQVD)Attributes.qvd (qvd);

[swuehl]

Since you want to a column 'Partner' to another table, I would suggest not to use LEFT JOIN, but a MAPPING solution (with the benefit to easily define a default value), this will even work if the record in the Attributes.qvd is completely missing.

MAP:

MAPPING

LOAD HPROP,

          SUBGROUP9

FROM $(vPathQVD)Attributes.qvd (qvd)

WHERE len(trim(SUBGROUP9));

YourTable:

LOAD HMY,

          //All other fields from your original table

          ApplyMap('MAP', HMY, 'Other') as Partner

FROM ...;