Selecting Data by Date range

[#1]

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!

[sunny_talwar]

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

[#3]

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.

[sunny_talwar]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[#6]

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!