Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AH
Creator III
Creator III

Problem with writing field values from another field

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

1 Solution

Accepted Solutions
sunny_talwar

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,

View solution in original post

7 Replies
AH
Creator III
Creator III
Author

Attached excel file.

maksim_senin
Partner - Creator III
Partner - Creator III

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

sunny_talwar

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

sunny_talwar

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:

Capture.PNG

Not applicable

Hi,

Please  find the attached QVW.

Hope it serves your purpose.

AH
Creator III
Creator III
Author

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

sunny_talwar

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,