topic Re: - in QlikView

how to exclude dates between dates that already exists

gradiske — Wed, 07 May 2014 13:45:03 GMT

Sorry guys, i was trying to edit with my smartphone yesterday and something went wrong =/

Re: How to exclude dates between dates that already exists

sudeepkm — Wed, 07 May 2014 14:08:30 GMT

you can load firstvalue(Period_ID) group by User_id.

Please take a look at the example.

Re: How to exclude dates between dates that already exists

gradiske — Wed, 07 May 2014 14:25:51 GMT

That's a good way to work around but, i have to consider period_id 4 too... since it's not between the period_id 1. And not always the first value it's the most large range between dates...

Re: How to exclude dates between dates that already exists

sudeepkm — Wed, 07 May 2014 14:30:28 GMT

oh sorry I completely forgot about it. let me consider the date.

Re: How to exclude dates between dates that already exists

Wed, 07 May 2014 15:11:23 GMT

Hi there,

Check out the attached file. I created a flag on the back-end and used that in set-analysis for sum. The flag works per your logic.

Hope it helps

Thanks

Re: How to exclude dates between dates that already exists

gradiske — Wed, 07 May 2014 16:41:04 GMT

You sir, made my day! Thank you so much!

Thank you too, Sudeep! For the effort!

But Ajay, could you explain to me the structure behind that? It will work to me but to be honest, i didn't understand a lot of whats going on in the script, hehe, specially the table with noconcatenate.

Thanks again!

Re: How to exclude dates between dates that already exists

Wed, 07 May 2014 17:17:59 GMT

Glad it worked for you.

In situations where you have to compare values with previous record values Peek() or Previous() functions come in handy. Also the table must be sorted using Order by.

Now I created new date fields Start and Final which will essentially replace the actual dates with values from previous record if condition is true. In this case dates of 2 and 3 lie within 1 so their dates will be replaced with 1's date. This will flag that record saying it shouldn't be included when summing, which is controlled in Set Analysis on the frond-end.

It will sum only if Flag =1.

Re: How to exclude dates between dates that already exists

gradiske — Wed, 07 May 2014 17:24:14 GMT

I've used Previous() before, and i actually thought about using it on this situation but i was kinda lost where and when to use it. It seems that everytime i've tried to solve this problem, it would escape more and more from the solution, hehe. Good to know that peek() works for this!

Thank you so much, sir!

Re: -

MarcoWedel — Thu, 08 May 2014 23:43:35 GMT

did you deliberately delete your question?

Re: -

gradiske — Fri, 09 May 2014 12:47:08 GMT

wow! o_O

Yesterday i was trying to edit with my smartphone and was giving me some errors, after a few times that i've tried, i gave up. That's weird!

Re: Re: How to exclude dates between dates that already exists

gradiske — Fri, 09 May 2014 12:55:49 GMT

Ajay, now i've got another situation. Using your example, sometimes this occurs:

userid	start_Date	final_Date	flag_delete	audience
35	20/06/2013	30/06/2013	N	0,36666666666667
35	22/07/2013	31/07/2013	N	0,33333333333333
35	22/07/2013	09/11/2013	N	3,7
35	02/09/2013	01/10/2013	Y	1
35	10/11/2013	03/12/2013	N	0,8

Note that the row n2 dates are between the row n3. Assuming that peek() are getting only the previous record, how can i handle that?