topic Re: Brick Wall in QlikView

Brick Wall

dmac1971 — Wed, 23 Jul 2014 15:09:48 GMT

Trying to get a resolution to what should be a simple problem, or I hope it is.

Lets say I have this table of data :

Ord No	Line No	Item	Desc	Qty	Price	Supplier
123456	1	1.4.2	Hat	2	2.00	Headlies
123546	2	1.4.5	Coat	3	3	RainWear
123546	3	1.3.2	Socks	1	1.5	Woolers
123546	3	1.3.2	Socks	1	1.5	Socksers
554478	1	1.4.2	Hat	1	2.00	Headlies
554478	2	1.4.5	Coat	3	3	RainWear
124582	1	1.4.2	Hat	2	2.00	Headlies
124582	2	1.4.5	Coat	3	3	Rainwear

And I am loading it in to a dashboard for order management.

For order 123456 there are two line 3's. I want to ignore any line that has more than 1 supplier, doesn't matter which one, the duplication is not required. I have created a key by joining the order no and line number, ie 123456-3, and want to disregard any line of data that is not unique, what is the best way to achieve this?

Re: Brick Wall

marcus_malinow — Wed, 23 Jul 2014 15:19:07 GMT

Dermot, try something like this:

Re: Brick Wall

Anonymous — Wed, 23 Jul 2014 15:21:08 GMT

Copy and paste this in a QVW and see if it works for your requirement.

Temp:

LOAD [Ord No],

[Line No],

Item,

Desc,

Qty,

Price,

Supplier

FROM

[http://community.qlik.com/thread/126822]

(html, codepage is 1252, embedded labels, table is @1);

Fact:

LOAD DISTINCT

[Ord No],

[Line No],

Item,

Desc,

Qty,

Price

Resident Temp;

Dim:

LOAD Distinct

[Ord No],

Supplier

Resident Temp;

Drop Table Temp;

Re: Brick Wall

dmac1971 — Wed, 23 Jul 2014 15:24:15 GMT

Doh never considered looking at the prev line for a match, I think the lines are in order as well so this may be a simple solution! Let me give it a try. Thanks guys.

Re: Brick Wall

dmac1971 — Wed, 23 Jul 2014 15:27:49 GMT

Lines are not in order so that's out the window! Michael I sorta understand what you are doing here, but why are there two Load Distincts?

Re: Brick Wall

marcus_malinow — Wed, 23 Jul 2014 15:30:57 GMT

It really doesn't matter that the lines are not in order.

LOAD 1 - load everything

LOAD 2 - resident load, and order everything

Re: Brick Wall

Anonymous — Wed, 23 Jul 2014 15:32:41 GMT

Marcus Solution will work with the peek since he is ordering the table with ORDER BY Ordline statment.

The second Distinct in my script is not necessary. Scripting is not my strongest skill set. With my Solution you can put Supplier in the same table but you will see a duplicate line entry for the transaction, however QlikView will still sum in as if it were one transaction on the total line. Just depends on the end user requirements.

Re: Brick Wall

dmac1971 — Wed, 23 Jul 2014 15:41:50 GMT

\o/ Brilliant, cheers guys that's it cracked!!!!