topic Problem with writing field values from another field in QlikView

Problem with writing field values from another field

AH — Mon, 10 Aug 2015 02:14:41 GMT

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

Re: Problem with writing field values from another field

AH — Mon, 10 Aug 2015 03:21:02 GMT

Attached excel file.

Re: Problem with writing field values from another field

maksim_senin — Mon, 10 Aug 2015 04:47:03 GMT

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

Re: Problem with writing field values from another field

sunny_talwar — Mon, 10 Aug 2015 05:03:32 GMT

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

Re: Problem with writing field values from another field

sunny_talwar — Mon, 10 Aug 2015 05:06:56 GMT

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:

Re: Problem with writing field values from another field

Mon, 10 Aug 2015 05:18:15 GMT

Hi,

Please find the attached QVW.

Hope it serves your purpose.

Re: Problem with writing field values from another field

AH — Thu, 13 Aug 2015 14:14:34 GMT

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

Re: Problem with writing field values from another field

sunny_talwar — Thu, 13 Aug 2015 14:16:27 GMT

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,