topic Re: Selecting Data by Date range in QlikView

Selecting Data by Date range

Sun, 21 Feb 2016 00:13:24 GMT

Hello Qlikview community!

For the past few weeks i've been studying qlikview and till this moment i've managed to get by my own with the information i can find on this community. But now i have struggled with this kind of problem.

By example i have this kind of data(have taken a small peace of it)

Date	Sum
1/10/2016	1.00
1/10/2016	-80,000.00
1/10/2016	-30,000.00
1/10/2016	80,000.00
1/12/2016	4.00
1/12/2016	30,000.00
1/12/2016	5.00
1/13/2016	6.00
1/14/2016	30,000.00
1/14/2016	7.00
1/14/2016	8.00
1/14/2016	9.00

The task i need for qlikview is to exclude all negative Sum data and only same positive Sum data that is located between the same or next 2 days in Date range data. Negative Sum data will always come first.

So the answer will look like this:

Datums	Sum
1/10/2016	1.00



1/12/2016	4.00

1/12/2016	5.00
1/13/2016	6.00
1/14/2016	30,000.00
1/14/2016	7.00
1/14/2016	8.00
1/14/2016	9.00

Thank you very much for your time to answer my question!

Re: Selecting Data by Date range

sunny_talwar — Sun, 21 Feb 2016 02:18:43 GMT

This seems like aggregated data, would it be possible to share raw data with the output you are looking from that data?

Re: Selecting Data by Date range

Sun, 21 Feb 2016 08:03:29 GMT

I'v created this sample data in excel to train with, cause I can't take the raw data at home. The raw data is qvd file created from Microsoft NaVision. The raw data have many more dimensions, but they are the same between each other for this task, so the only difference is in fields Date and Sum.

But here are my excel and qvw files.

Did i understand you question write?

Thank you.

Re: Selecting Data by Date range

sunny_talwar — Sun, 21 Feb 2016 14:54:18 GMT

I understand what you are trying to do, but unfortunately I don't think I have a way to solve this. May be swuehl‌ or gwassenaar‌‌ have a solution for you.

Best,

Sunny

Re: Selecting Data by Date range

Gysbert_Wassenaar — Sun, 21 Feb 2016 15:25:11 GMT

Try this:

Check:

LOAD

Autonumber(Date + IterNo() -1 & '|' & -Amount) as Check

FROM

[Hide Date.xlsx] (ooxml, embedded labels, table is Sheet2)

WHILE

IterNo() <=3;

Data:

LOAD Date, Amount

FROM

[Hide Date.xlsx] (ooxml, embedded labels, table is Sheet2)

WHERE

Not Exists(Check, AutoNumber(Date & '|' & Amount))

AND Amount >= 0;

DROP TABLE Check;

Re: Selecting Data by Date range

Sun, 21 Feb 2016 16:04:22 GMT

Just tried this code and it worked exactly as I was looking for. Thank you very much gwassenaar‌ and sunindia‌ for your help. I appreciate it!

Have a nice day!