Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) ;
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?
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
Could you please share your application
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;