Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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);
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]
Thank you, this works!
Thank you!
Awesome, I am glad it did