Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mattdt1811
Contributor III
Contributor III

Scripting for Zero Values

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?


14 Replies
mattdt1811
Contributor III
Contributor III
Author

Hi sasiparupudi1,


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


Many Thanks.

sasiparupudi1
Master III
Master III

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

mattdt1811
Contributor III
Contributor III
Author

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 ...

sasiparupudi1
Master III
Master III

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

mattdt1811
Contributor III
Contributor III
Author

Hi Sasu,

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

Thank you in advance.