topic Re: How to link data from excel in QlikView

How to link data from excel

abc_18 — Tue, 10 Apr 2018 04:39:11 GMT

Hi,

I need to exclude some 'order number' in my dashboard report because they are causing duplicate records.

I am trying to store those order number in excel , so later if I want to remove from report I can easily delete from excel.

As app is in production, so can't change every time in Prod.

How can I achieve this, please suggest.

Thanks

Re: How to link data from excel

saimahasan — Tue, 10 Apr 2018 05:55:09 GMT

How frequent those order numbers will change(added/subtracted) in your data??

Re: How to link data from excel

bramkn — Tue, 10 Apr 2018 06:28:46 GMT

Load the Excel file with the field named something different than the fields already in use. Than load with a where not exists on that field.

Re: How to link data from excel

abc_18 — Tue, 10 Apr 2018 08:00:25 GMT

Hi Saima,

Not Frequently, may be in a month

Re: How to link data from excel

saimahasan — Tue, 10 Apr 2018 10:03:56 GMT

So for that you can make monthly change in excel if you don't have any other tagging in your data to find the exclusion orders.

Re: How to link data from excel

abc_18 — Tue, 10 Apr 2018 10:14:47 GMT

We don't want to do any change in excel monthly wise.

Is there any solution that,we can exclude those order numbers in report?

Re: How to link data from excel

abc_18 — Tue, 10 Apr 2018 10:17:24 GMT

Hi Bram,

I tried this, but still getting those records.

Excel:

LOAD

"Duplicate order"

FROM [lib://2.External_data/Backorder.xlsx](ooxml, embedded labels, table is Sheet1)

Where not Exists ([Duplicate order]);

Re: How to link data from excel

saimahasan — Tue, 10 Apr 2018 10:50:04 GMT

Is there any indicator in your data basis which you identify those orders??

Re: How to link data from excel

bramkn — Tue, 10 Apr 2018 11:04:02 GMT

Hi the load with the where not exists statement should go at the normal order table. so where you want to delete those records.

Re: How to link data from excel

abc_18 — Tue, 10 Apr 2018 11:51:26 GMT

HI Bram,

Can you please explain, then what's the need to maintain those records in separate excel

Re: How to link data from excel

bramkn — Tue, 10 Apr 2018 12:56:51 GMT

When you save the order numbers to be deleted in the Excel file. Everytime you reload it will discard these records.

When loading the order table put where not exists on the bottom.

Re: How to link data from excel

vvira1316 — Tue, 10 Apr 2018 13:29:14 GMT

May be you can use resident table an peek function to identify existing values...

I'm adding some example that are used for performance optimization but I feel it may help you...

Case1. Count( Distinct ‘FieldName’).

The distinct qualification, especially if text strings are read, is costly. A useful technique

is to assign the value ‘1’ to each new value as the field is read:

Load

Alfa,

if (peek('Alfa')=Alfa,0,1) as Flag1,

Num

resident table_1

order by Alfa Asc;

Here the “peek” compares the value of Alfa being read with that previously read. If the

values are the same “Flag” is set to 0, if they are different “Flag” is set to 1. The number

of distinct values will then be = sum(Flag). Please note that the list has to be ordered

and that when using “order by” in a load resident QlikView orders the list before starting

to read.

You can leverage flag to keep or remove records. I hope it helps.