Scripting for Zero Values

[mattdt1811]

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?

[vishsaggi]

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

[mattdt1811]

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.

[Frank_Hartmann]

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!

[mattdt1811]

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.

[qlikviewwizard]

Hi Matthew Baldwin

Try like this example.

[sasiparupudi1]

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

[mattdt1811]

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.

[sasiparupudi1]

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

[mattdt1811]

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.