Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
What would be the expected o/p
This is the option I've trying now.
I will post if it works.
Thanks for the reply
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
No problem at all and keep us posted
Have you tried with FirstSortedValue() Function
Can you please share me the full script
Hi Ivan,
1 Example:
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..
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 .