topic Re: Scripting for Zero Values in QlikView

Scripting for Zero Values

mattdt1811 — Fri, 28 Sep 2018 15:38:24 GMT

Hi All,

I have the below script to insert zero values into my data where it does not exist against a date, which works for data for myself.

Concatenate(TEMP1)

load 'Paid in UK' as FreightPaid,

'Insured' as INSURED,

TempDate as [CREATION DATE],

'BALDWIN@M' as [USER CREATED BY],

1 as Dummy,

0 as JOBCOUNTER

Resident TempCalendar;

Drop Table TempCalendar;

My fact Table is TEMP1 and I have a master calendar in the script generating all dates.

How would I edit this section of script to cater for all [USER CREATED BY] possibilities?

Re: Scripting for Zero Values

vishsaggi — Fri, 28 Sep 2018 18:14:37 GMT

Did not get you, can you elaborate with an example or sample data please?

Re: Scripting for Zero Values

mattdt1811 — Mon, 01 Oct 2018 08:33:27 GMT

Hi,

My issue was getting 0 values into my line chart in which I was advised to use the above/attached script.

The example attached works for my own USER ID in the line chart however, I will need to get it to work for all USER ID's, Customers etc.

If you need anything else please me know.

Thank you.

Re: Scripting for Zero Values

Frank_Hartmann — Mon, 01 Oct 2018 08:57:21 GMT

what are you expecting to see in the linechart when selecting 2017? a red line between Jul-Aug and Oct-Nov?

if yes then either go to axes tab and set StaticMin to -0.5 or disable smooth option in expressionstab!

if not then please give more information about what you have and what you want to achieve!

hope this helps!

Re: Scripting for Zero Values

mattdt1811 — Mon, 01 Oct 2018 09:08:05 GMT

Hi Frank,

The line chart shows what I would want to for my own data I have loaded in the example.

However, once I load all my other data in, other user id's show the chart like below (not one continuous line dropping down and showing the 0's)

If I repeat my dummy value script again;

Concatenate(TEMP1)

load 'Paid in UK' as FreightPaid,

'Insured' as INSURED,

TempDate as [CREATION DATE],

'NEW USER ID' as [USER CREATED BY],

1 as Dummy,

0 as JOBCOUNTER

Resident TempCalendar;

This chart will work.

My problem is I have over 1000+ USER ID's so inserting the script for each of them is not possible.

I need to script for it to loop through and apply this to all user id's (to start with) - i will then need to apply to all customers and other fields.

Many Thanks.

Re: Scripting for Zero Values

qlikviewwizard — Mon, 01 Oct 2018 09:47:26 GMT

Hi Matthew Baldwin

Try like this example.

Re: Scripting for Zero Values

sasiparupudi1 — Mon, 01 Oct 2018 09:55:25 GMT

May be like below?

USERDATES:

LOAD

Distinct

'Paid in UK' as FreightPaid,

'Insured' as INSURED,

TempDate as [CREATION DATE],

1 as Dummy,

0 as JOBCOUNTER

Resident TempCalendar;

Join(USERDATES)

LOAD DISTINCT

'NEW USER ID' as [USER CREATED BY],

Resident YOURUSERTABLE;

Concatenate(TEMP1)

Load

Resident USERDATES;

hth

Re: Scripting for Zero Values

mattdt1811 — Mon, 01 Oct 2018 10:57:03 GMT

Thank you Sasidhar,

This does work perfectly for [USER CREATED BY].

Now on to my next issue if you are able to assist further?

If I also LOAD DISTINCT [CLIENT NAME], the execution of script becomes very slow and the UI crashes. I think this is because I have 37,586 client names and it inserts a 0 for each Client Name and each date? The script log says it producing 333,000,000 lines!

Is there away around this?

Maybe I am approaching it wrong and instead of inserting 0 into my fact table for each possible record, I should be inserting 0 into the Month & Year?

Many Thanks.

Re: Scripting for Zero Values

sasiparupudi1 — Mon, 01 Oct 2018 11:03:30 GMT

Cross joins should be cautiously used.. Would you be able to share a sample to look at the issue?

Re: Scripting for Zero Values

mattdt1811 — Mon, 01 Oct 2018 14:04:49 GMT

Hi Sasidhar,

Examples QVW and type of Data I am working with attached - I hope I have included enough for you to work with. I think in essence it is

all the same as my master data.

I believe you will just need to insert the source for the data from your local settings in below;

You will notice how the chart is working well when selected USER CREATED BY from your script but not for CLIENT NAME or other fields.

Thank you.

Re: Scripting for Zero Values

mattdt1811 — Thu, 04 Oct 2018 09:26:36 GMT

Hi sasiparupudi1‌,

I was just wondering if you have been able to take a look at the example I sent please?

Many Thanks.

Re: Scripting for Zero Values

sasiparupudi1 — Thu, 04 Oct 2018 20:53:23 GMT

May be try

Concatenate(TEMP1)

LOAD

Distinct [USER CREATED BY],

'Paid in UK' as FreightPaid,

'Insured' as INSURED,

Date($(varMinDate) + IterNo() - 1) as [CREATION DATE] ,

1 as Dummy,

0 as JOBCOUNTER

Resident TEMP1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Re: Scripting for Zero Values

mattdt1811 — Fri, 05 Oct 2018 09:33:01 GMT

The chart works well again based on selection of USER CREATED BY on the UI - on the example QVW sent previously.

However it does still not work when I select either a CLIENT NAME or HUB CODE

I have also tried loading this into my master file and it is very slow in executing the script ...

Re: Scripting for Zero Values

sasiparupudi1 — Fri, 05 Oct 2018 12:21:34 GMT

Try changing your expression to

Alt(sum({$<FreightPaid={'Paid in UK'},INSURED={'INSURED'}>}JOBCOUNTER),0)+Avg(0)

comment the join and concat in the script

HTH

Sasu

Re: Scripting for Zero Values

mattdt1811 — Mon, 08 Oct 2018 08:02:44 GMT

Hi Sasu,

Would you be able to send an example at all please?

Thank you in advance.