Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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:

ID | Storehouse_ID |      onDate      | Customer_ID | Bill_goods | Invoice |   Total_Amount   |

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) ;

4 Replies
petter
MVP
MVP

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?

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Could you please share your application

aarkay29
Specialist
Specialist

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;