topic Re: Setting a default value on a column within a load statement in QlikView

Setting a default value on a column within a load statement

kschramm — Sat, 08 Aug 2015 12:20:58 GMT

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);

Re: Setting a default value on a column within a load statement

brenner_martina — Sat, 08 Aug 2015 12:28:17 GMT

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

Re: Setting a default value on a column within a load statement

sunny_talwar — Sat, 08 Aug 2015 14:33:30 GMT

May be this:

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

Re: Setting a default value on a column within a load statement

swuehl — Sat, 08 Aug 2015 15:47:49 GMT

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 ...;