Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Brick Wall

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 NoLine NoItemDescQtyPriceSupplier
12345611.4.2Hat22.00Headlies
12354621.4.5Coat33RainWear
12354631.3.2Socks11.5Woolers
12354631.3.2Socks11.5Socksers
55447811.4.2Hat12.00Headlies
55447821.4.5Coat33RainWear
12458211.4.2Hat22.00Headlies
12458221.4.5Coat33Rainwear

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?

1 Solution

Accepted Solutions
Specialist III
Specialist III

Dermot, try something like this:

View solution in original post

7 Replies
Specialist III
Specialist III

Dermot, try something like this:

View solution in original post

Creator III
Creator III

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;

Creator II
Creator II

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.

Creator II
Creator II

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?

Specialist III
Specialist III

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

LOAD 1 - load everything

LOAD 2 - resident load, and order everything

Creator III
Creator III

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.

Creator II
Creator II

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