Adding an artificial field via load script

[#1]

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 · Accepted solution]

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]

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]

[#4]

Thank you, this works!

[#5]

Thank you!

[sunny_talwar]

Awesome, I am glad it did