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: 
Not applicable

Items existing in a table

Hi

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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,-,-

];

View solution in original post

13 Replies
swuehl
MVP
MVP

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

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

Not applicable
Author

Hi Swuehl

Will do.

Kind regards

Nayan

Not applicable
Author

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

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

-

-

Item 18

18.95

17.95

18.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

-

-

-

swuehl
MVP
MVP

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,-,-

];

Not applicable
Author

Hi Nayan,

try this

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


It will works...

Not applicable
Author

Hi

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

Not applicable
Author

Hi Swuehl

Thanks, it works. J

Regards

Nayan

adnan_rafiq
Partner - Creator II
Partner - Creator II

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.

Not applicable
Author

Hi Adnan

Thank you . Will try it too.

Kind regards

Nayan