topic Re: Advise - scripting in QlikView

Advise - scripting

joris_lansdaal — Sat, 04 Jun 2016 21:21:06 GMT

Hi,

I would like some advise and would like to know if I my below script is written efficient.Do I need to reload the tables as much as I do to create the new variables?

Thanks.

Temp_Demand_2:

Load

BQS_ind,

BQS_oms,

Weekday_Flag,

Demand_Datum,

Demand_Datum_Month,

BQS_Dat_ID,

If(isnull(Demand_Aantal)=-1,0,Demand_Aantal) as Demand_Aantal,

Datum,

Flag_uit,

Leverancier_Naam,

Lopend_Crediteurnr,

Lopend_Crediteuroms,

Lopend_Invoerdat,

Lopend_Afldat,

Lopend_Orderbak,

Lopend_Order_interval,

Lopend_Overdue,

Lopend_confirmatie,

Lopend_Open,

Lopend_Order,

If(isnull(Lopend_Aantal_B)=-1,0,Lopend_Aantal_B) as Lopend_Aantal_B,

If(isnull(Lopend_Aantal_O)=-1,0,Lopend_Aantal_O) as Lopend_Aantal_O,

If(isnull(Lopend_Aantal_T)=-1,0,Lopend_Aantal_T) as Lopend_Aantal_T,

Artnr,

Lopend_Artnr,

Lopend_Artoms,

Joris,

1 as Flag,

If(isnull(Voorraad)=-1,0,Voorraad) as Voorraad,

//---------------------------------------Projectie Fact-------------------------------------------------------

If(isnull(Demand_Aantal)=-1,0,Demand_Aantal)*-1+

If(isnull(Lopend_Aantal_B)=-1,0,Lopend_Aantal_B) +

If(isnull(Voorraad)=-1,0,Voorraad) as Projectie_step

resident Temp_Demand

where Flag_uit=0 and isnum(BQS_ind)

Order by BQS_ind asc, Datum asc, Demand_Aantal desc;

//-------------------------Nu maken we een rangesum van Projectie_Step-----------------------------------------

Temp_Projectie_1:

Load *,

BQS_ind&Datum as %BQS_Datum_Key,

ApplyMap('Voorraadprijs_max',BQS_ind,0)as Voorraadprijs_max,

ApplyMap('Voorraadprijs_min',BQS_ind,0)as Voorraadprijs_min,

if(BQS_ind=Peek(BQS_ind),RangeSum(Projectie_step,Peek(Projectie2)),RangeSum(Projectie_step)) as Projectie2

Resident Temp_Demand_2;

//-------------------------Omdat er meerdere lijnen op 1 dag kunnen zijn moeten we de max bepalen---------------

Projectie:

Mapping

Load

BQS_ind&Datum as %BQS_Datum_Key,

Max(Projectie2) as Projectie

resident Temp_Projectie_1

Group by BQS_ind&Datum ;

// ------------------------- Met Peek plaatsen we de max waarde op de eerste lijn en een 0 op de andere-------------------

Demand:

Load *,

if(if(%BQS_Datum_Key=Peek(%BQS_Datum_Key),0,ApplyMap('Projectie',%BQS_Datum_Key,0))<0,0,if(%BQS_Datum_Key=Peek(%BQS_Datum_Key),0,ApplyMap('Projectie',%BQS_Datum_Key,0)))as Projectie

resident Temp_Projectie_1;

//------------------------- nu hebben we de juiste dagprojecties op de weekdagen --------------------------------------

Drop table Temp_DagVoorraad, Temp_Demand, Temp_Demand_2, Temp_Projectie_1;

Store Demand into $(vStorePath)\Datamodel_Projection.CSV (txt);

Re: Advise - scripting

Peter_Cammaert — Sun, 05 Jun 2016 10:01:04 GMT

Can you explain what the script should do/find? The way you formatted your load script doesn't exactly make it look like a User Manual...

Re: Advise - scripting

joris_lansdaal — Sun, 05 Jun 2016 10:31:06 GMT

Good point about the formatting. Are there any guidelines on how to improve script readability; next to the format?

Re: Advise - scripting

joris_lansdaal — Sun, 05 Jun 2016 10:46:25 GMT

The script your looking at needs to generate one measure named Projectie. There can be multiple lines per BQS_ind - Date combination. I need one value per BQS_Ind -Date combination for Projectie.

1) First I created Temp_Demand2 where I loaded/concatenated 3 different sources. Inventory, Orders and future Demand. Three measures combined gives me the first step for projection measure.

2) Via a correct sorting and rangesum I can extract a Max value and apply mapping.

3) in order to apply one value and zero's on the other lines I created the for the Demand table.

It works, but is ask myself if I need to create all the different tables?

Re: Advise - scripting

swuehl — Sun, 05 Jun 2016 11:14:42 GMT

You might be able to use a preceding load instead of resident loads, e.g. when looking at your first two table load statements.

Preceding Load

Note that there recently were discussions about the performance costs of a preceding load being to high, so test with your data to ensure the costs are not too high.

You may also be able to replace the aggregating mapping tables and additional resident loads with the ApplyMap() functions with loading your fact table two times, once sorted asc, once des, to create the entries for the first and last line of your grouping entities.

Re: Advise - scripting

Peter_Cammaert — Sun, 05 Jun 2016 11:15:13 GMT

The table you can drop for sure is the mapping table. If you don't have billions of rows, replace it with

LEFT JOIN (Temp_Projectie_1)

LOAD BQS_ind, Datum, Max(Projectie2) as NieuweProjectie

RESIDENT Temp_Projectie_1

GROUP BY BQS_in, Datum;

and in your next step, you use the new field instead of the original one.

Also (but this doesn't elimnate any tables, it's just shorter in writing) all the IF() tests for null values can be replaced by the Alt() function which does the same. For example

If(isnull(Demand_Aantal)=-1,0,Demand_Aantal) as Demand_Aantal,

becomes

Alt(Demand_Aantal, 0) AS Demand_Aantal,

BTW IMHO GROUP BY can only group on field names, not on expressions (which 'BQS_in & Datum' is) Don't you get any errors?

Best,

Peter

Re: Advise - scripting

swuehl — Sun, 05 Jun 2016 12:02:43 GMT

Hi Peter,

AFAIK, GROUP BY can indeed group by expression, I am using this like

...

GROUP BY MonthName(DATEFIELD);

so using a combined key that doesn't exist in the input table should also work (which should be the same than grouping on the fields separately).

Re: Advise - scripting

Peter_Cammaert — Sun, 05 Jun 2016 12:18:34 GMT

Thanks Stefan. I should search for an experiment I did a few years back, when the use of ad-hoc fields always made the GROUP BY fail. I gave up since then. Remember discussing the state of QlikView documentation? This is what it says in the help article on the LOAD statement:

At the top: "[ group_by groupbyfieldlist ]"

In the discussion for group by: "groupbyfieldlist ::= (fieldname { ,fieldname } )"

and back to the top: "fieldname is a text that is identical to a field name in the table. "

The on-line help isn't any different. I know that my restricted field-of-vision is caused in part by Qlik information It's still a pity that they don't do anything about it...

Peter

Re: Advise - scripting

joris_lansdaal — Sun, 05 Jun 2016 12:26:29 GMT

Peter and Stefan,

I really appreciate both your help, thanks!

Joris

Re: Advise - scripting

swuehl — Sun, 05 Jun 2016 21:36:34 GMT

The on-line help isn't any different. I know that my restricted field-of-vision is caused in part by Qlik information It's still a pity that they don't do anything about it...

Peter

Hi Peter,

yeah, I remember that discussion pretty well.

I fully agree that the help pages should be reviewed more often.

It would really be good if we somehow can post comments / issues to the help page technical writers.

Best

Stefan