topic Adding an artificial field via load script in QlikView

Adding an artificial field via load script

Fri, 27 Nov 2015 07:52:04 GMT

Can anyone advise me on how can I achieve this in the load script?

As you can see from my load script, I have managed to select the first 7 characters for company codes that start with 'T' or 'S'..

Directory;

LOAD [Company Code],

[Company Name],

Data,

Left([Company Code],7) as [Primary Key]

FROM

[File source.xlsx]

(ooxml, embedded labels, table is Sheet1);

However, for Company Code that starts with 'S', I need the values to be blank or '-' under the column Primary Key.

In addition, I need to retain Company Code in both groups of Company Codes 'S' & 'T' in the final table.

Therefore wildmatch() function is not usable, as it reduces the rows in my final table.

sunny_talwar — Fri, 27 Nov 2015 12:45:53 GMT

Try something like this:

Directory;

LOAD [Company Code],

[Company Name],

Data,

If(Left([Company Code], 1) = 'T', Left([Company Code],7)) as [Primary Key]

FROM

[File source.xlsx]

(ooxml, embedded labels, table is Sheet1);

Anonymous — Fri, 27 Nov 2015 12:48:21 GMT

Hello,

try to replace this line Left([Company Code],7) as [Primary Key] by:

if(Left([Company Code],1)= 'T', Left([Company Code],7), '') as [Primary Key]

Sun, 29 Nov 2015 23:56:39 GMT

Thank you, this works!

Sun, 29 Nov 2015 23:57:31 GMT

Thank you!

sunny_talwar — Mon, 30 Nov 2015 10:51:44 GMT

Awesome, I am glad it did