Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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