topic Re: If statement in Load Script - Load other data in QlikView

If statement in Load Script - Load other data

alwinsch — Thu, 29 Aug 2013 14:30:53 GMT

Hi all,

Below you can find my load script,

what i'am trying to do is, load all data, if one column has data = 1

then get a new select statement to run

Problem is:

My column 'Bestemmeling_code' has 2 values

- Department

-User

I want to load i my final table only one column

so if Value is Department my finale table with column Destination has to be

the name of the department otherwise the same column has to be the name of the user.

Please help how to solve this... I think i 'am wrong around the if statement.

kind regards,

Alwin

----

DATA_TEMP:
SELECT
ag_agnd.p_ag_agnd as Id,
projct_name as Project,
mentn_descrptn as Omschr_Melding,
c_destntn_type as Bestemmeling_Type,
ag_agnd_d.destntn_id as Bestemmeling_Code
from ag_agnd
left join ag_projct on ag_projct.p_ag_projct = ag_agnd.p_ag_projct //--- project
left join tb2_valeurall_plus soort_melding on soort_melding.valeur = ag_agnd.c_mentn_part and soort_melding.code like '0203'
;

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

for i_teller=0 to $(noRows)

let a=Peek('Id',$(i_teller),'DATA_TEMP');
let b=Peek('Project',$(i_teller),'DATA_TEMP');
let c=Peek('Omschr_Melding',$(i_teller),'DATA_TEMP');
let d=Peek('Soort_Melding',$(i_teller),'DATA_TEMP');
let s=Peek('Bestemmeling_Type',$(i_teller),'DATA_TEMP');
let t=Peek('Bestemmeling_Code',$(i_teller),'DATA_TEMP');

Data:
Load
'$(a)' as Id,
'$(b)' as Project,
'$(c)' as Omschr_melding,
'$(d)' as Soort_melding,
'$(t)' as Bestemmeling_Code,
if ('$(s)' = 2,
---- SELECT
--------- Bestemmeling.user_name as Bestemmeling,
----- from sys_users Bestemmeling
----- where bestemmeling.user_id = '$(t)'
)
;
next

drop table DATA_TEMP;

Re: If statement in Load Script - Load other data

kaushiknsolanki — Thu, 29 Aug 2013 14:41:50 GMT

Hi,

Do you mean that you want to change the filed name depending on some value?

Regards,

Kaushik Solanki

Re: If statement in Load Script - Load other data

alwinsch — Thu, 29 Aug 2013 14:47:47 GMT

not change the field name, the value has to come from different table in SQL

if Bestemmeling_Type = 2 then

select name from users where...

else if bestemmeling_type = 1 then

select name from department where...

endif

--> put this value in new column 'Destination'

Regards,

Re: If statement in Load Script - Load other data

kaushiknsolanki — Thu, 29 Aug 2013 14:55:35 GMT

Hi,

See there are two options, 1. You can write the sql query which can do this for you.

2. you manipulate in qlikview.

So qlikview script will look like this.

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

for i_teller=0 to $(noRows)

Temp_User:

Sql distinct Select Username from xyz where Parameter = $(t);

Let vUser = peek('Username',0,'Temp_User');

Temp_Dept:

Sql distinct Select Department from xyz where Parameter = $(t);

Let vDept = peek(Department,0,'Temp_Dept);

Load

'$(a)' as Id,

'$(b)' as Project,

'$(c)' as Omschr_melding,

'$(d)' as Soort_melding,

'$(t)' as Bestemmeling_Code,

if ('$(s)' = 2,'$(vUser)','$(vDept)') as New Field;

Resident DATA_TEMP;

next...

Regards,

Kaushik Solanki

Re: If statement in Load Script - Load other data

alwinsch — Fri, 30 Aug 2013 07:49:48 GMT

Thanks,

I did it in SQL, seems to be better then in qv script.

Regards,