Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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()
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
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.
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()
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
Thank you very much