topic Re: Delete or ignore table rows when loading data in QlikView

Delete or ignore table rows when loading data

antonaks — Mon, 07 Aug 2017 08:12:21 GMT

Please help in following problem.
Is it possible to delete (or not load) table rows by condition?

For example, something like this:
If SalesDate = '21 .05.2016 'and FilialKey =' 25615' DELETE ROW ...

I need to ignore part of the lines when loading the script.

Thank you in advance for your help.

Re: Delete or ignore table rows when loading data

Gysbert_Wassenaar — Mon, 07 Aug 2017 08:21:41 GMT

You can add a where clause to a load statement to filter out records you don't want to load:

LOAD *

FROM MyTable.qvd (qvd)

WHERE SalesDate <> '21 .05.2016 'and FilialKey <>' 25615'

Re: Delete or ignore table rows when loading data

techvarun — Mon, 07 Aug 2017 08:25:16 GMT

to Exclude write something like

WHERE SalesDate <>'21 .05.2016 'and FilialKey <> '25615'

Re: Delete or ignore table rows when loading data

antonaks — Mon, 07 Aug 2017 09:18:49 GMT

Thank you, but this is not exactly what I need. With this solution I have excluded from the table is fully specified Filial and Date. But I need to delete the row at the intersection of the Filial code and Date.

Re: Delete or ignore table rows when loading data

techvarun — Mon, 07 Aug 2017 10:20:22 GMT

The code will delete the intersected records only.

share a sample piece of data and expected output if possible

Re: Delete or ignore table rows when loading data

antonaks — Mon, 07 Aug 2017 11:48:45 GMT

I think I understood what the problem is. I did not correctly formulate the question. Below is an example of what I want to get.

Initial data:

FilialKey	Date	Data
33525	15.07.2017	6 017
33525	16.07.2017	3 924
33525	17.07.2017	5 933
33525	18.07.2017	6 069
33525	19.07.2017	8 002
33525	20.07.2017	6 458
33525	21.07.2017	6 751
33525	22.07.2017	3 379
33525	23.07.2017
33525	24.07.2017
33525	25.07.2017
33525	26.07.2017
33525	27.07.2017
33525	28.07.2017
33526	05.07.2017	3 924
33526	06.07.2017	5 933
33526	07.07.2017	6 069
33526	08.07.2017	8 002
33526	09.07.2017	6 017
33526	10.07.2017	7 322
33526	11.07.2017	8 274
33526	12.07.2017	9 488
33526	13.07.2017	347
33526	14.07.2017
33526	15.07.2017
33526	16.07.2017

Output data:

FilialKey	Date	Data
33525	15.07.2017	6 017
33525	16.07.2017	3 924
33525	17.07.2017	5 933
33525	18.07.2017	6 069
33525	19.07.2017	8 002
33526	05.07.2017	3 924
33526	06.07.2017	5 933
33526	07.07.2017	6 069
33526	08.07.2017	8 002
33526	09.07.2017	6 017

In the case described above, two conditions must work:

1) Date<= '19.07.2017' and FilialKey = 33525

2) Date<= '09.07.2017' and FilialKey = 33526

I have about 50 such paired conditions. Is it possible to use them in one query?

Re: Delete or ignore table rows when loading data

antonaks — Mon, 07 Aug 2017 14:26:57 GMT

Bingo!
I figured it out. A fairly good option works with the use of "OR"

WHERE

FilialKey = '33502' and Date<= '26.07.2017' or

FilialKey = '33525' and Date<= '21.07.2017' or

FilialKey = '33534' and Date<= '25.07.2017' or

FilialKey = '33540' and Date<= '09.06.2017' or

....