Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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;

1 Reply

Re: IF Statement in Script

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

Community Browser