13 Replies Latest reply: Sep 5, 2013 7:21 AM by Nayan Lalla RSS

    Items existing in a table

    Nayan Lalla

      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

        • Re: Items existing in a table
          Stefan Wühl

          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
              Nayan Lalla

              Hi Swuehl

               

               

               

              Will do.

               

               

               

              Kind regards

               

              Nayan

              • Re: Items existing in a table
                Nayan Lalla

                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

                 

                -

                 

                -

                 

                -

                  • Re: Items existing in a table
                    Stefan Wühl

                    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

                  Hi Nayan,

                   

                   

                  try this

                   

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


                  It will works...

                  • Re: Items existing in a table
                    Adnan Rafiq

                    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
                      Adnan Rafiq

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