IF Statement in Script

Wed, 03 Jun 2015 13:40:28 GMT

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 — Wed, 03 Jun 2015 15:24:38 GMT

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

topic IF Statement in Script in QlikView

IF Statement in Script

Re: IF Statement in Script