topic Re: Required explanation in QlikView

Required explanation

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Please expalin the below logic.

load

Supplier_Id as Supplier_Num, if(index(Supplier_Id, 3)='AND',mid(Supplier_Id,4)&'-32455' ,mid(Supplier_Id,4)&'-12245') as [Supplier ID]

From [..\5_QVD\D_CAP_Scar_Supplier.qvd] (qvd) where left(Supplier_Id,3) = 'AND' or left(Supplier_Id,3) = 'IS1';

Thanks..

Re: Required explanation

Anonymous — Wed, 22 Nov 2017 06:31:23 GMT

Hi Naresh,

It looks like you are creating a Supplier ID field based on few conditions such as

index(Supplier_Id, 3)='AND' and mid(Supplier_Id,4)&'-32455' ,mid(Supplier_Id,4)&'-12245'

-Jai

Re: Required explanation

nareshthavidishetty — Wed, 22 Nov 2017 06:48:47 GMT

Yes..

I need to know how this condtion works.

Thanks..

Re: Required explanation

jyothish8807 — Wed, 22 Nov 2017 06:50:55 GMT

Hi,

index(Supplier_Id, 3)='AND' is a wrong condition. Output of index function wil be always numeric.

Re: Required explanation

Anonymous — Wed, 22 Nov 2017 07:31:47 GMT

Hi,

as Jyothish already mentioned, the condition would not work.

As I understand the code the field Supplier_Id is stored as Supplier_Num first.

After that it is checked if there is "AND" within the Supplier_Id. If so, the string starting with character no. 4 with added "-32455" is stored as Supplier_Id. If not, it will attach "-12245" to the same string and store it as Supplier_Id.

But there are 2 problems within the logic.

1. What Jyothish said. Index will give a number. If you want to check for a substring within another string, it could be like this:

if(index(Supplier_Id,'AND')>0,.......

2. As I already said, it will take the substring from character no. 4 on, but as 'AND' has 3 characters, 'ND' would be stored also. So in addition to 1.) I would suggest the following:

if(index(Supplier_Id,'AND')>0,MID(Supplier_Id,index(Supplier_Id,'AND')+3)&'-32455'),Supplier_Id&'-12245') as [Supplier ID]

I hope that helps!

regards!