topic Re: Items existing in a table in QlikView

Items existing in a table

Tue, 03 Sep 2013 08:14:27 GMT

I have items that appear on more than one price lists. If a item appears on say, Price List A, it must give me '2' otherwise it must give me '1'.

How do i do the scripting on the front end.

I've tried this formula if(Price List = 'Price List A', 2,1) , however the result for that item appears on two lines, which i dont want.

Please can you help.

kind regards

Nayan

Re: Items existing in a table

swuehl — Tue, 03 Sep 2013 08:28:22 GMT

Could you post a small sample app with your expected outcome?

It's quite hard to help you without knowing almost anything.

Re: Items existing in a table

Tue, 03 Sep 2013 09:01:50 GMT

Hi Swuehl

Will do.

Kind regards

Nayan

Re: Items existing in a table

Tue, 03 Sep 2013 09:52:50 GMT

Hi Swuehl

Below is an extract of the table im working with. It’s a list of items which appearing in different price lists.

My query is : If an item appears in Price list A, give me ‘2’ otherwise give me ‘1’.

So, for eg, Item 9 , appears in Price List A, hence the result I want in a is ‘2’. For Item 8, it does not appear in Price list A, hence the result should be ‘1’.

(please note the values in the columns below are relevant for the scripting I want to achieve).

Hope this helps.

Kind regards

Nayan

PRICE LISTS

Item Code

Item 1

59.95

83.95

Item 2

71.95

Item 3

83.95

Item 4

71.95

Item 5

83.95

Item 6

49.95

Item 7

84.95

Item 8

145.95

Item 9

154.95

139.95

129.95

Item 10

119.95

119.00

Item 11

189.95

201.11

189.95

Item 12

119.95

107.95

Item 13

129.95

Item 14

48.95

Item 15

69.95

79.95

Item 16

79.95

Item 17

42.95

49.95

Item 18

18.95

17.95

18.95

Item 19

89.95

Item 20

107.95

Item 21

94.95

89.95

Item 22

95.00

Item 23

139.00

Item 24

222.00

Item 25

132.00

139.95

Item 26

189.95

Item 27

189.00

179.00

Item 28

420.00

379.00

Item 29

79.95

Item 30

320.00

Item 31

799.00

Item 32

399.00

Item 33

399.00

Item 34

699.00

Re: Items existing in a table

swuehl — Tue, 03 Sep 2013 10:07:41 GMT

That's quite hard to read.

I assume your data looks like shown in this inline table.

Then it might be enough to check if there is a number in column A:

if(isnum(A),2,1) as FlagPriceListA

Here is the full script snippet:

Set DecimalSep = '.';

Set ThousandSep = ',';

PRICE_LISTS:

LOAD *,

if(isnum(A),2,1) as FlagPriceListA

INLINE [

Item Code,A,B,C,D

Item 1,59.95,83.95,-,-

Item 2,-,71.95,-,-

Item 3,-,83.95,-,-

Item 4,-,71.95,-,-

Item 5,-,83.95,-,-

Item 6,-,49.95,-,-

Item 7,-,84.95,-,-

Item 8,-,145.95,-,-

Item 9,154.95,139.95,129.95,-

Item 10,119.95,119.95,119.95,119.00

Item 11,189.95,201.11,-,189.95

Item 12,119.95,119.95,-,107.95

Item 13,-,129.95,-,-

Item 14,48.95,48.95,48.95,48.95

Item 15,69.95,79.95,-,-

Item 16,-,79.95,-,-

Item 17,42.95,49.95,-,-

];

Re: Items existing in a table

Tue, 03 Sep 2013 10:08:13 GMT

Hi Nayan,

try this

if(count(Price List) >1, 2,1)

It will works...

Re: Items existing in a table

Tue, 03 Sep 2013 10:30:02 GMT

Im not sure why the table came out that way. Thanks for your feedback, I’ll try your formula.

By the way, how do you reply on the website. There used to be the function to reply.

Just to let you know, the table should look like this. I’ve taken a screen shot. (Hope it comes out right)

Kind regards

Nayan

Re: Items existing in a table

Thu, 05 Sep 2013 07:42:40 GMT

Hi Swuehl

Thanks, it works. J

Regards

Nayan

Re: Items existing in a table

adnan_rafiq — Thu, 05 Sep 2013 07:50:32 GMT

try Match function ,

it will be like this, if(Match(Item, PricelistA) > 1,2,1). I have seen this function work sometimes where condition of equal fails in if statement.

Re: Items existing in a table

Thu, 05 Sep 2013 07:52:16 GMT

Hi Adnan

Thank you . Will try it too.

Kind regards

Nayan

Re: Items existing in a table

adnan_rafiq — Thu, 05 Sep 2013 08:40:31 GMT

You can also try exist function if you are not comparing row by row

Re: Items existing in a table

Thu, 05 Sep 2013 09:10:16 GMT

Hi Adnan

I have not used the exist function before. How would I apply it.

Kind regards

Nayan

Re: Items existing in a table

adnan_rafiq — Thu, 05 Sep 2013 09:40:16 GMT

you will apply in your script, basically like

load if(exists(item),2,1) but for that you have to rename pricelist2 field

as item. or you can chose any other name.

It will check if item name you are currently loading exist in previously in

the same field. that , if pricelistA has item value "abc" and you are

loading item "abc" it will make condition true and return 2 else 1.

Re: Items existing in a table

Thu, 05 Sep 2013 11:21:05 GMT

Thank you Adnan.