If statement in Load Script - Load other data

[alwinsch]

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;

[kaushiknsolanki]

Hi,

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

Regards,

Kaushik Solanki

[alwinsch]

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,

A.

[kaushiknsolanki · Accepted solution]

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.

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');

     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

[alwinsch]

Thanks,

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

Regards,

A.