Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Dermot, try something like this:
Dermot, try something like this:
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;
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.
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?
It really doesn't matter that the lines are not in order.
LOAD 1 - load everything
LOAD 2 - resident load, and order everything
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.
\o/ Brilliant, cheers guys that's it cracked!!!!