Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have three columns in excel file. One is InvoiceID, ItemID and another is Sales Description. For InvoiceID which starts with ''SI" does not have an ItemID if you look at the excel file. Please see Sheet1 tab in excel file
What i am trying to get is the InvoiceID that starts with "SI" will bring the Sales Description into ItemID and make it an ItemID for that InvoiceID. Please see the Sheet2 tab.
What would be the correct script for this to achieve the goal?
I tried with this: If (left(InvoiceID,2)=’SI’ and ItemID=’’,SalesDescription,ItemID) as ItemID
Thanks in advance.
Regards,
Ahmed
Do you mean 'SI' or 'CN'?
May be this:
If(Match(Left(InvoiceID,2), 'SI', 'CN') and Len(Trim(ItemID)) = 0, SalesDescription, ItemID) as ItemID,
Attached excel file.
Hi Ahmed,
If your script does not work as expected (I didn't try) then try using IsNull(ItemID) instead of ItemID=''.
Best regards,
Maxim
May be this:
If(Left(InvoiceID,2)=’SI’ and Len(Trim(ItemID)) = 0,SalesDescription, ItemID) as ItemID
If(Left(InvoiceID,2)= 'SI' and Len(Trim(ItemID)) = 0, SalesDescription, ItemID) as ItemID
Try the following Script:
Table:
LOAD InvoiceID,
If(Left(InvoiceID,2)= 'SI' and Len(Trim(ItemID)) = 0, SalesDescription, ItemID) as ItemID,
SalesDescription
FROM
Book4.xlsx
(ooxml, embedded labels, table is Sheet1);
Output:
Hi,
Please find the attached QVW.
Hope it serves your purpose.
Hi Sunny,
Thanks for your reply.
If I have InvoiceID started with 'SI' and 'CN' and what would be the code change here?
Thanks in Advance.
Regards,
Ahmed
Do you mean 'SI' or 'CN'?
May be this:
If(Match(Left(InvoiceID,2), 'SI', 'CN') and Len(Trim(ItemID)) = 0, SalesDescription, ItemID) as ItemID,