1 Reply Latest reply: Jun 3, 2015 11:24 AM by Sunny Talwar RSS

    IF Statement in Script

      I have a table called OS and i want to create an other table with just the address values that doesnt repeat (like using distinct).

      Ex: the OS ID = 3 and 4 have the same address value so the ADD_TEMP table will get only four values.

      So i used a if statement to know if the address is already on ADD_TEMP table. If not it will be putted on and then i will make a google maps call to get the address lat/long.

       

      The problem is: the qlikview doesn't accept the if statement condition.

       

      Someone help me?

       

       

      [OS]:

      load * inline [

          ID, address, service_type

          1, 'Av. Dr. José Augusto Moreira, 505, Olinda', A

          2, 'carlos de lima cavalcanti, 1886, Olinda', B

          3, 'Av. Dr. José Augusto Moreira, Olinda', C

          4, 'Av. Dr. José Augusto Moreira, Olinda', B

          5, 'Av. Dr. José Augusto Moreira, 1187, Olinda', A

      ];

       

       

      [ADD_TEMP]:

      LOAD * INLINE [

          address2

      ];

       

      let noRows = NoOfRows('OS')-1;

       

      for i=0 to $(noRows)

                let addr = peek('address',$(i),'OS');

       

                if SubStringCount(Concat(address2, ';'), '$(addr)') = 0 then

                    [ADD_TEMP]:

                    LOAD * INLINE [

                    address2

                    '$(addr)'

                    ];

       

                     //AFTER I KNOW THAT THE ADDRESS IS UNIQUE

                     //CALL GOOGLE MAPS FOR THIS ADDRESS TO GET LAT/LONG

       

                ENDIF;

       

      next i;

        • Re: IF Statement in Script
          Sunny Talwar

          You might need to create a variable with a concatenated list instead of using Concat function in the if statement. May be something like this:

           

          [OS]:

          load * inline [

              ID, address, service_type

              1, 'Av. Dr. José Augusto Moreira, 505, Olinda', A

              2, 'carlos de lima cavalcanti, 1886, Olinda', B

              3, 'Av. Dr. José Augusto Moreira, Olinda', C

              4, 'Av. Dr. José Augusto Moreira, Olinda', B

              5, 'Av. Dr. José Augusto Moreira, 1187, Olinda', A

          ];

           

          ConcatAddress:

          LOAD Concat(DISTINCT address, ';') as ConcatAdd

          Resident OS;

           

          LET vConcatAdd = Chr(39 & Peek('ConcatAdd') & Chr(39);

           

          Drop Table ConcatAddress;

           

          [ADD_TEMP]:

          LOAD * INLINE [

              address2

          ];

           

          let noRows = NoOfRows('OS')-1;

           

          for i=0 to $(noRows)

                    let addr = peek('address',$(i),'OS');

           

                    if SubStringCount($(vConcatAdd)), '$(addr)') = 0 then

                        [ADD_TEMP]:

                        LOAD * INLINE [

                        address2

                        '$(addr)'

                        ];

           

                        //AFTER I KNOW THAT THE ADDRESS IS UNIQUE

                        //CALL GOOGLE MAPS FOR THIS ADDRESS TO GET LAT/LONG

           

                    ENDIF;

           

          next i;



          I hope this will help.

           

          Best,

          Sunny