topic Re: Setup Variables into the Script from resident in QlikView

Setup Variables into the Script from resident

Anonymous — Fri, 15 Jul 2016 16:11:50 GMT

Hello,

I would like to declare some variables:

Here my script:

How can i improve this script ? Do you have some advise for me please ?

Because i find some mistakes into the variables ...

Thank you in advance for your help,

Sébastien.

Re: Setup Variables into the Script from resident

sunny_talwar — Fri, 15 Jul 2016 16:49:15 GMT

1) I would save the variables as Num instead of dates just because its very easy to convert a numeric value to date on the front end.

2) Secondly may be you can create a list of sources and look through them instead of having to concatenate this multiple times.

3) I don't really think you need Distinct, but having it in there doesn't do any harm.

Re: Setup Variables into the Script from resident

Anonymous — Fri, 15 Jul 2016 16:50:19 GMT

Thanks you for your help !

Re: Setup Variables into the Script from resident

sunny_talwar — Fri, 15 Jul 2016 16:50:20 GMT

If you can copy paste your script, I might be able to provide you with an updated script with points 1 and 3. Point 2 which you can try if you really want to, otherwise this would work as well

Re: Setup Variables into the Script from resident

Anonymous — Fri, 15 Jul 2016 16:52:48 GMT

Thanks you !

Here my Script (modified):

Variables:

load Distinct Date(MAX(AsOf), 'DD/MM/YYYY') as LastAsOf1

resident [All_Data] where source = 'Projection_CTY';

Let Last_Update_Projection = Date(Peek('LastAsOf1'), 'DD/MM/YYYY');

Concatenate

load Distinct Date(MAX(AsOf), 'DD/MM/YYYY') as LastAsOf2

resident [All_Data] where source = 'Costs_PC';

Let Last_Update_Costs = Date(Peek('LastAsOf2'), 'DD/MM/YYYY');

Concatenate

load Distinct Date(MAX(AsOf), 'DD/MM/YYYY') as LastAsOf3

resident [All_Data] where source = 'Staff_Kert';

Let Last_Update_Staff = Date(Peek('LastAsOf3'), 'DD/MM/YYYY');

Concatenate

load Distinct Date(MAX(AsOf), 'DD/MM/YYYY') as LastAsOf4

resident [All_Data] where source = 'NBI_Data';

Let Last_Update_NBI = Date(Peek('LastAsOf4'), 'DD/MM/YYYY');

Concatenate

load Distinct Date(MAX(AsOf), 'DD/MM/YYYY') as LastAsOf5

resident [All_Data] where source = 'RWA_CTY';

Let Last_Update_RWA = Date(Peek('LastAsOf5'), 'DD/MM/YYYY');

Concatenate

load Distinct Date(MAX(AsOf), 'DD/MM/YYYY') as LastAsOf6

resident [All_Data] where source = 'Budget_NBI';

Let Last_Update_Budget_NBI = Date(Peek('LastAsOf6'), 'DD/MM/YYYY');

Re: Setup Variables into the Script from resident

sunny_talwar — Fri, 15 Jul 2016 16:58:03 GMT

May be try this:

Variables:

LOAD RowNo() as Key,

MAX(AsOf) as LastAsOf1

Resident [All_Data]

Where source = 'Projection_CTY';

Join (Variables)

LOAD RowNo() as Key,

MAX(AsOf) as LastAsOf1

Resident [All_Data]

Where source = 'Costs_PC';

Join (Variables)

LOAD RowNo() as Key,

MAX(AsOf) as LastAsOf1

Resident [All_Data]

Where source = 'Staff_Kert';

Join (Variables)

LOAD RowNo() as Key,

MAX(AsOf) as LastAsOf1

Resident [All_Data]

Where source = 'NBI_Data';

Join (Variables)

LOAD RowNo() as Key,

MAX(AsOf) as LastAsOf1

Resident [All_Data]

Where source = 'RWA_CTY';

Join (Variables)

LOAD RowNo() as Key,

MAX(AsOf) as LastAsOf1

Resident [All_Data]

Where source = 'Budget_NBI';

LET Last_Update_Projection = Peek('LastAsOf1');

LET Last_Update_Costs = Peek('LastAsOf2');

LET Last_Update_Staff = Peek('LastAsOf3');

LET Last_Update_NBI = Peek('LastAsOf4');

LET Last_Update_RWA = Peek('LastAsOf5');

LET Last_Update_Budget_NBI = Peek('LastAsOf6');