topic Re: Group by syntax in QlikView

Group by syntax

Mon, 13 Jul 2015 21:54:32 GMT

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

Re: Group by syntax

swuehl — Mon, 13 Jul 2015 22:10:06 GMT

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],

[Start Date],

[End Date],

[Supplier ID],

[Supplier Name],

[Destination Company],

[Item Code],

Brand,

[Invoice Date],

[Invoice status],

Currency,

[Discount amount],

[Net amount],

[Other Charges];

Removed the ALIASed expressions in the GROUP BY.

Re: Group by syntax

Tue, 14 Jul 2015 06:35:24 GMT

Thanks for reply,

it works but the duplicates are still there. any ideas

Thanks

Re: Group by syntax

maksim_senin — Tue, 14 Jul 2015 06:38:24 GMT

Hi,

Try adding DISTINCT after LOAD, i.e. LOAD DISTINCT...

But please keep in mind this since sometimes duplicates are not evil but just a fact.

Best regards,

Maxim

Re: Group by syntax

swuehl — Tue, 14 Jul 2015 06:55:37 GMT

Can you give an example of these duplicate lines and what you expect instead?

A Distinct Load might be a solution (probably without the GROUP BY then), as Maxim said, have you already tried it ?

Re: Group by syntax

Tue, 14 Jul 2015 07:02:41 GMT

yes, I tried distinct load but distinct does not consider the record as duplicate if only one field is matching.

example of data is as follows

Supplier ID	Supplier Name	Destination Company	Invoice No	Item Code	Brand	Invoice Date	Invoice status	Currency	Gross amount	Discount amount	Net amount	Other Charges
123	opi supplier	ksa	1	opi1	opi	1-Dec-14	cleared	USD	2000	200	1800	300
456	x supplier	uae	2	onetech1	onetech	1-Jan-15	in progress	EUR	3000	300	2700	200
456	x supplier	uae	3	REVLON1	REVLON	1-Jan-15	in progress	EUR	4000	400	3600	100
123	opi supplier	ksa	4	opi1	opi	1-Dec-13	cleared	USD	2000	200	1800	300
456	x supplier	uae	5	onetech1	onetech	1-Jan-14	in progress	EUR	3000	300	2700	200
456	x supplier	uae	6	REVLON1	REVLON	1-Jan-14	in progress	EUR	4000	400	3600	100
123	opi supplier	ksa	7	opi1	opi	1-Dec-15	cleared	USD	2000	200	1800	300
456	x supplier	uae	8	onetech1	onetech	1-Jan-16	in progress	EUR	3000	300	2700	200
789	z supplier	uae	9	REVLON1	REVLON	1-Jan-16	in progress	EUR	4000	400	3600	100
1111	z supplier	uae	9	REVLON1	REVLON	12-Jul-16	in progress	EUR	1000	400	3600	100

Re: Group by syntax

swuehl — Tue, 14 Jul 2015 07:08:10 GMT

For this sample data, what do you expect instead?

Re: Group by syntax

Peter_Cammaert — Tue, 14 Jul 2015 07:12:21 GMT

Your example only has DISTINCT rows.

DISTINCT works across all columns in a row, meaning that a row has to have identical values in all fields to be kicked out.

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

Best,

Peter

Re: Group by syntax

Peter_Cammaert — Tue, 14 Jul 2015 07:20:09 GMT

Which one do you want to keep? The most recent one (latest Invoice Date)?

Re: Group by syntax

Peter_Cammaert — Tue, 14 Jul 2015 07:24:00 GMT

Try this:

Actuals:

NOCONCATENATE

LOAD [Invoice No]

Max([Invoice Date]) AS [Invoice Date]

RESIDENT Actual

GROUP BY [Invoice No];

LEFT JOIN (Actuals)

LOAD * RESIDENT Actual;

Does not work however if you have multiple invoices with identical IDs on the same date. But then your original data set is weird as well (multiple invoices with the same ID on different dates?)

Re: Group by syntax

Tue, 14 Jul 2015 07:32:50 GMT

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.

Re: Group by syntax

Peter_Cammaert — Tue, 14 Jul 2015 07:36:56 GMT

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

Re: Group by syntax

Tue, 14 Jul 2015 07:41:36 GMT

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

just one last question, why the NOCONCATENATE?

Re: Group by syntax

Tue, 14 Jul 2015 07:44:23 GMT

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

Re: Group by syntax

Peter_Cammaert — Tue, 14 Jul 2015 08:59:42 GMT

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