topic Re: How to remove previously loaded row, if either of two fields has a specific value ? in QlikView

How to remove previously loaded row, if either of two fields has a specific value ?

Fri, 17 Feb 2017 12:26:49 GMT

Hello,

I use qlik view for a couple of months ( so I am kind of a newbie ). I work on a project and unfortunatelly i got stuck into something..

So, here is my issue:

I have a qvd, which contains sales data. I want to filter this sales data when I load it.

Example of two rows data:

1 | 15 | 2017-02-16 | 3 | 1 | 0 | 12.34 |

2 | 15 | 2017-02-17 | 3 | 1 | 1 | 12.34 |

So, the issue comes when I try to display the sales from 2017-02-16 to 2017-02-17. I got 24.68, its true because QV has loaded 2 sales (above rows). I have created a unique key - " Storehouse_ID & '|' & onDate & '|' & Customer_ID as TestKey "

But, I want to implement this logic:

if current sale ( which I load ), matches my TestKey, check if I have already loaded a sale with the same TestKey and

if current sale has Invoice 1 and the previous sale has bill_goods 1 -> delete the previous sale

else just load the current sale

Here is how I load the table and create a unique key:

sales:

LOAD

Storehouse_ID & '|' & onDate & '|' & Customer_ID as TestKey,

FROM sales.qvd(qvd) ;

Re: How to remove previously loaded row, if either of two fields has a specific value ?

petter — Mon, 20 Feb 2017 01:19:50 GMT

Could you please clarify your requirements by making more example data that show the logic. I can't figure out what you mean without having some more rows of data showing exactly what you read and the results you want to happen after the logic has been applied.

Do you read two source tables or is it only one table from a single QVD?

What do you mean by "current sale" do you mean current record/row that you are reading from a QVD?

Re: How to remove previously loaded row, if either of two fields has a specific value ?

Anil_Babu_Samineni — Mon, 20 Feb 2017 01:28:08 GMT

May be this?

If(Match(Invoice,'1'),1,0) as MatchTestKey

Then you can use this Flag where ever you want

Sum({<MatchTestKey = {'1'}>} Measure)

Or you may describe with expected result as your input

Re: How to remove previously loaded row, if either of two fields has a specific value ?

Mon, 20 Feb 2017 02:04:08 GMT

Could you please share your application

Re: How to remove previously loaded row, if either of two fields has a specific value ?

aarkay29 — Mon, 20 Feb 2017 02:12:10 GMT

TRY THIS:

LOAD *

WHERE FLAG<>'X';

LOAD *,

IF(TestKey=PEEK(TestKey),

IF(PEEK(Invoice,-1)=1 AND PEEK(Bill_goods,0)=1,'X')) AS FLAG

RESIDENT

TABLE

ORDER BY

TestKey,Invoice DESC;