topic Re: Design - maybe Logic issue in QlikView

Design - maybe Logic issue

QFanatic — Mon, 13 Apr 2020 16:22:49 GMT

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

Re: Design - maybe Logic issue

dplr-rn — Mon, 13 Apr 2020 18:46:29 GMT

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/1470637

Re: Design - maybe Logic issue

QFanatic — Mon, 13 Apr 2020 19:05:23 GMT

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.

Re: Design - maybe Logic issue

dplr-rn — Mon, 13 Apr 2020 19:14:56 GMT

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

Re: Design - maybe Logic issue

QFanatic — Mon, 13 Apr 2020 19:21:07 GMT

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

Re: Design - maybe Logic issue

dplr-rn — Mon, 13 Apr 2020 19:26:12 GMT

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

Re: Design - maybe Logic issue

QFanatic — Tue, 14 Apr 2020 05:59:31 GMT

Thank you very much