topic Re: Duplicates on multiple Excel Loads in QlikView

Duplicates on multiple Excel Loads

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a problem with the Load of script with Excel:

I have multiple Excel books in a folder that i Load with:

FROM

[$(QvDocumentBaseName)\DS\ORDENES*.xlsx]

To catch all Excels that start with ORDENES.

It works but, the problem is:

EXCEL 1 EXCEL2

PK1 | Value1 PK2 | Value3

PK2 | Value2 PK3 | Value3

PK3 | Value3 PK4 | Value4

The table i obtain is (i use Distinct on load):

PK1 | Value1

PK2 | Value2

PK2 | Value3

PK3 | Value3

PK4 | Value4

I try with temporal table with aggregation and resident load after but it don't work.

There any way to avoid this duplicates? The duplicated value that i want to obtain (PK2) is indifferent, i need only conserve one of those registers.

Thanks in advance!

Regards,

Ivan.

Re: Duplicates on multiple Excel Loads

sunny_talwar — Mon, 10 Oct 2016 15:36:49 GMT

May be use loop instead of wildcard to load your data and with each subsequent load use a where exist statement. Information on loop can be found here: Loops in the Script

Re: Duplicates on multiple Excel Loads

Anil_Babu_Samineni — Mon, 10 Oct 2016 15:37:31 GMT

What would be the expected o/p

Re: Duplicates on multiple Excel Loads

Mon, 10 Oct 2016 15:43:06 GMT

This is the option I've trying now.

I will post if it works.

Thanks for the reply

Re: Duplicates on multiple Excel Loads

Mon, 10 Oct 2016 15:45:16 GMT

This:

PK1 | Value1

PK2 | Value2

PK3 | Value3

PK4 | Value4

Or this:

PK1 | Value1

PK2 | Value3

PK3 | Value3

PK4 | Value4

Instead of this:

PK1 | Value1

PK2 | Value2

PK2 | Value3

PK3 | Value3

PK4 | Value4

Re: Duplicates on multiple Excel Loads

sunny_talwar — Mon, 10 Oct 2016 15:48:52 GMT

No problem at all and keep us posted

Re: Duplicates on multiple Excel Loads

Anil_Babu_Samineni — Mon, 10 Oct 2016 16:10:29 GMT

Have you tried with FirstSortedValue() Function

Can you please share me the full script

Re: Duplicates on multiple Excel Loads

el_aprendiz111 — Mon, 10 Oct 2016 20:11:48 GMT

Hi Ivan,

1 Example:

Re: Duplicates on multiple Excel Loads

Tue, 11 Oct 2016 08:02:35 GMT

Hi Sunny,

Now i'm trying with your solution, but i'm getting fewer records than expected. This is the test:

For each vFileName in Filelist ('$(QvDocumentBaseName)\DS\OPERACIONES*.xlsx')
  [tmp]:
  Load
  Order &'-'& [Operation/Activity]   as "$(HidePrefix)1Order-OP-PK",
  Order                              as "1Order",
    [Operation/Activity]             as "1Operation/Activity",
    [Operation short text]           as "1Operation short text",
    [Planner group]                  as "1PlannerGroupOperaciones",
    Equipment                        as "1Equipment Operaciones",
    [Description of technical object] as "1Technical Description",
    [Functional Location] as "1FLOperaciones",
    [Description of functional location] as "1DescriptionFL",
    [Order Type] as "1Order Type Operaciones",
    [Activity Type] as "1Activity Type",
    [Latest finish date] as "1Latest finish date",
    [Reference date] as "1RD",
    [Actual work] as "1Actual work",
    [Normal duration] as "1Normal duration",
    [Actual start (date)] as "1Actual start (date)"
  From [$(vFileName)]
  (biff, ooxml, embedded labels, table is Sheet1)
  WHERE not Exists(Order) and not Exists([Operation/Activity]);
  Next vFileName

The PK to avoid the duplicates are the compose of Order and Operation/Activity.

I'ts for the Where clause, but i don't know how to fix it..

Re: Duplicates on multiple Excel Loads

Tue, 11 Oct 2016 10:17:00 GMT

Hi el_aprendiz111‌‌,

I,ve been testing your solution an it works in my case.

I'm gonna testing the results with the data, but the number of registers is good!

Thanks to all for your replies .

Re: Duplicates on multiple Excel Loads

Tue, 11 Oct 2016 13:10:49 GMT

Done! it's ok.

Thanks !

Re: Duplicates on multiple Excel Loads

sunny_talwar — Tue, 11 Oct 2016 16:45:04 GMT

Are all the files getting loaded? or is it skipping one of the files? Logfile would be a good place to analyze this information

Re: Duplicates on multiple Excel Loads

Anonymous — Thu, 13 Oct 2016 07:01:53 GMT

Hi Ivan,

When you are loading your data, you can use exists. But, if you have values that have to be accumulated or maximized, you will load all your data, and then, accumulate or calulate the max value on new table.

Regards!