Group by syntax

[#1]

Hello,

I'm trying to eliminate duplicate invoices resulting from data entry mistakes in excel and I'm using firstvalue function and group by function. I keep getting invalid expression error. can anyone look at the script and spot the mistake.

Actuals:

LOAD

[Invoice No],

[Supplier ID]&'|'&[Start Date]&'|'&[End Date] as KEY,

[Start Date],

[End Date],

[Supplier ID],

[Supplier Name],

[Destination Company],

[Item Code],

Brand,

[Invoice Date],

InYearToDate([Invoice Date],Today(),Year([Invoice Date])-Year(today())) as PINYTD,

[Invoice status],

Currency, 

[Discount amount],

[Net amount],

[Other Charges],

FirstValue([Gross amount])

Resident Actual

GROUP BY

[Invoice No],

[Supplier ID]&'|'&[Start Date]&'|'&[End Date] as KEY,

[Start Date],

[End Date],

[Supplier ID],

[Supplier Name],

[Destination Company],

[Item Code],

Brand,

[Invoice Date],

InYearToDate([Invoice Date],Today(),Year([Invoice Date])-Year(today())) as PINYTD,

[Invoice status],

Currency, 

[Discount amount],

[Net amount],

[Other Charges];

Thanks

[#2]

Its not logical to have multiple invoices with same ID but since I'm relying on data entry person on excel, I have to consider that he will forget changing invoice ID.

[Peter_Cammaert]

Sorry, I clearly read your original post without paying attention. My bad.

P.

[#4]

I though about this method but I was worried about performance but it works. thanks

just one last question, why the NOCONCATENATE?

[#5]

the below also works but it takes long time to add the first value for all the fields when you want to add data validation.

keep only one row for example per Supplier ID, use FirstValue() or FirstSortedValue() on all other fields and GROUP BY Suppleir ID

[Peter_Cammaert]

Call it a long-time-QlikView-use automatism. It doesn't hurt and blocks QlikView from autoconcatenating when you least expect it. Leave it out if you want.

Good luck,

Peter