Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gradiske
New Contributor

how to exclude dates between dates that already exists

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

1 Solution

Accepted Solutions
Not applicable

Re: How to exclude dates between dates that already exists

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

AJ

10 Replies
sudeepkm
Valued Contributor III

Re: How to exclude dates between dates that already exists

you can load firstvalue(Period_ID) group by  User_id.

Please take a look at the example.

gradiske
New Contributor

Re: How to exclude dates between dates that already exists

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

sudeepkm
Valued Contributor III

Re: How to exclude dates between dates that already exists

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

Not applicable

Re: How to exclude dates between dates that already exists

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

AJ

gradiske
New Contributor

Re: How to exclude dates between dates that already exists

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!

Not applicable

Re: How to exclude dates between dates that already exists

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.

gradiske
New Contributor

Re: How to exclude dates between dates that already exists

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

did you deliberately delete your question?

gradiske
New Contributor

Re: -

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!

Community Browser