Design - maybe Logic issue

[QFanatic]

hi guys, am at my Wits end with this one.

This (scrambled data) model contains transactions per UCN (Unique identification Number) ,per Channel, per Date,

Now I want to see for the last 3 Calendar days (field called DateSelector) what the volumes were. So if one person (UCN 5672 528 has not logged in for the last 3 days, i want to see, in the chart ZERO for all three days.

Person 25642 102 has indeed produced volume for 11th and 12th. So I want to see those 2 dates, plus a Zero for the 13th. 

I dont know how to get this right...

1. maybe I should not create DateSelector field in my script - rather use variables in the chart?

2. My joins - its almost like I have to 1) do a left join to the Full Calendar to find all the missing days, and then..

3. Do the join the other way around - i.e. left join, again to the initial (entry table - in hidden script) to see which days has missing info?

I dont know...please, any help.

thank you

[dplr-rn]

Issue is because there is no data for those dates for that user. because its 'null' for all the possible dates  user doesnt show up

you might need to generate some data for the 3 days with 0s 

https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/14706...

[QFanatic]

Hello, thank you for your reply.. I think we're on the right track!

In Henric's example, he has a rate per day. So I have a "rate", per day, per customer.

His Script...   TempTable_Rates:
     Load Date, Rate From Rates ;

Would all of mine then just change to look at the #3 columns, instead of his 2? And all the rest of mine will work in 3's?

Not sure how that would affect the peek logic.

Much appreciated.

[dplr-rn · Accepted solution]

not 100% clear on your question.

but roughly i would say load zero value for latest day always in your script with a zero

something like below 

Concatenate(MC_1)
	load *,
	    today() as CAL_DATE,
	     'Last 3 days' as  DateSelector,  
	     0 as VOLUME
	     ;
	load distinct
	     UCN, 
	     FULL_NAME    
	resident MC
	;
//existing code below
Drop Table MC;

you could even add a where clause to the resident e.g. where cal_date<today()

[QFanatic]

Hi, what I mean that in Henrics code he only has a rate per day. I have a
rate per day, per person (Ucn).

I will have a look and let you know. Thank you

#- Please type your reply above this line -##

[dplr-rn]

you have to do at the granularity that you have in your data. so if you have rate per day per customer you need to generate data like that

[QFanatic]

 Thank you very much