topic Re: SELECT - from duplicate rows under condition in QlikView

SELECT - from duplicate rows under condition

sculptorlv — Tue, 08 Dec 2015 10:29:43 GMT

Hello, I hope for your help.

I have table with following columns (and values in rows):

ID Name Sign

1 ABC 0

2 DD 0

3 CC 1

4 ABC 1

5 DD 0

I need such select to get the same columns, but another rows:

- only 1 row with the same "Name" (name should be unique)

- if all rows with the same "Name" have value 0, then the first row only

- if rows with the same "Name" have value 1, then the first row only

-if rows with the same "Name" have 1 and 0, then first row with value 1

The result shold be the following:

ID Name Sign

2 DD 0

3 CC 1

4 ABC 1

Thanks for your help in advance.

Re: SELECT - from duplicate rows under condition

ecolomer — Tue, 08 Dec 2015 15:18:08 GMT

Is this?

Re: SELECT - from duplicate rows under condition

maxgro — Tue, 08 Dec 2015 15:53:20 GMT

source:

load * inline [

ID Name Sign

1 ABC 0

2 DD 0

3 CC 1

4 ABC 1

5 DD 0

] (delimiter is spaces);

final:

NoConcatenate load *

Resident source

Where Peek('Name') <> Name

Order By Name, Sign desc;

DROP Table source;

Re: SELECT - from duplicate rows under condition

effinty2112 — Tue, 08 Dec 2015 16:28:12 GMT

Hi Ruslans,

Here's another way:

Data:

LOAD * INLINE [

ID, Name, Sign

1, ABC, 0

2, DD, 0

3, CC, 1

4, ABC, 1

5, DD, 0

];

Inner Keep(Data)

AggrData:

LOAD

Name,

Max(Sign) as Sign

Resident Data Group by Name;

Drop Table AggrData;

Inner Keep(Data)

Result:

LOAD

Min(ID) as ID,

Name,

FirstValue(Sign) as Sign

Resident Data Group by Name;

Drop Table Result;

Suggest you use maxgro's suggestion which is much better.

Re: SELECT - from duplicate rows under condition

sculptorlv — Wed, 09 Dec 2015 06:43:27 GMT

Thank you all for answers.

I didn't mentioned

I don't use LOAD, but SELECT from regular data base table.

Thus, I have a lot of columns and rows .. and I show only one simple example in order to get general idea, how to solve my problem.

Re: SELECT - from duplicate rows under condition

maxgro — Wed, 09 Dec 2015 18:48:54 GMT

maybe

select a.ID, a.Name, a.Sign from

(select name, sign, min(ID) as ID from Table_2 group by name, sign) a

inner join

(select Name, max(Sign) as Sign from Table_2 group by Name) b

on a.Name = b.Name and a.Sign = b.sign

order by a.ID

table top, query result bottom

Re: SELECT - from duplicate rows under condition

sculptorlv — Thu, 17 Dec 2015 13:54:01 GMT

Thank you!

This thing work, but I use many difficult equations. Thus I have to get rid of multiple rows on SQL level

Re: SELECT - from duplicate rows under condition

sculptorlv — Thu, 17 Dec 2015 13:58:35 GMT

I got syntax error as soon as I put MAX() ...

Re: SELECT - from duplicate rows under condition

effinty2112 — Thu, 17 Dec 2015 15:42:52 GMT

Hi Ruslans,

You could consider a preceding load in your script. It may be a matter of taste I like to keep SELECT statements as simple as possible. It often makes sense to put WHERE clauses here but other than that I prefer to do any renaming, aggregating etc in the preceding load.

Whatever way you go I hope you get a solution.

Cheers

Re: SELECT - from duplicate rows under condition

maxgro — Thu, 17 Dec 2015 17:58:09 GMT

Applied-to Scan Result"

you have a " only on the right

and I think also you have to group by only by "Document No_"

Re: SELECT - from duplicate rows under condition

sculptorlv — Fri, 18 Dec 2015 06:43:54 GMT

Hello Andrew!

Can you please write a little bit more detailed example about using LOAD.

I tried to read manuals, but until now didn't get the idea about using it in my problem.

For now I have database in MS SQL and I joined several tables.

Then I need to remove duplicated rows by Name with only one biggest value from Sign (see first post)

Thank you in advance.

Re: SELECT - from duplicate rows under condition

sculptorlv — Fri, 18 Dec 2015 06:54:32 GMT

Thank you. I really did a mistake with ".

Now I get the result, but it not removed duplicated rows:

(Document No_ is my "Name" and Applied-to-Scan is my "Sign")

If I remove Applied from Group by, I get mistake:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Column 'EUR-VENDEN.dbo.EUR VENDEN$Document Rows.Applied-to Scan Result' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Re: SELECT - from duplicate rows under condition

maxgro — Fri, 18 Dec 2015 12:19:53 GMT

for a group by to works

select

field1, field2,

sum(field3), count(field4), avg(field5), .......

from table

where ...

group by field1, field2;

the fields without sum or count or ..... must be in the group by

you have field1 and field2 in the select list

you don't use an aggregation function (sum ,....) for field1 and field2

so field1 and field2 must be in the group by

Re: SELECT - from duplicate rows under condition

sculptorlv — Fri, 18 Dec 2015 13:53:52 GMT

Thank you! I finally get the result I needed.

Can share code for other, if someone will read this topic.

SQL SELECT

Tbl_DocumentRows."Document No_" AS Document_Number,

MAX(Tbl_DocumentRows."Applied-to Scan Result") AS Applied,

Tbl_DocumentHeaders."New FA Location Code" AS IN_Location,

Tbl_DocumentHeaders."FA Location Code" AS OUT_Location,

DAY(Tbl_DocumentHeaders."Posting Date") AS Day,

MONTH(Tbl_DocumentHeaders."Posting Date") AS Month,

YEAR(Tbl_DocumentHeaders."Posting Date") AS Year,

Tbl_FACLASS.Code AS FA_cLASS,

(CASE Tbl_DocumentHeaders."Doc_ Type"

WHEN '1' THEN 'Uz kontraktu'

WHEN '2' THEN 'Uz degustaciju'

WHEN '3' THEN 'No kontrakta'

WHEN '4' THEN 'No degustacijas'

WHEN '5' THEN 'Profilakse'

WHEN '6' THEN 'Pardosana'

WHEN '7' THEN 'Nonemt no uzskaites'

WHEN '8' THEN 'Ieksejais parvietojums'

WHEN '9' THEN 'Uz ekspluataciju'

WHEN '10' THEN 'Noma' END) AS Document_Type,

(CASE Tbl_DocumentHeaders."Doc_ Reason"

WHEN '1' THEN 'Jauns klients'

WHEN '2' THEN 'Papildus iekarta'

WHEN '3' THEN 'Parregistracija'

WHEN '4' THEN 'Klases maina'

WHEN '5' THEN 'No degustacijas uz ligumu'

WHEN '6' THEN 'Udens un pudeles'

WHEN '7' THEN 'Uz pasakuma periodu'

WHEN '8' THEN 'Iekartas samazinajums'

WHEN '9' THEN 'Beidzas kontrakts'

WHEN '10' THEN 'Beidzas degustacija'

WHEN '11' THEN 'Pec pasakuma laika'

WHEN '12' THEN 'Profilakse'

WHEN '13' THEN 'Pardosana'

WHEN '14' THEN 'Nonemts no uzskaites'

WHEN '15' THEN 'Ieksejaias parvietojums'

WHEN '16' THEN 'Uz ekspluataciju'

WHEN '17' THEN 'Noma'

WHEN '18' THEN 'Rent' END) AS Document_Reason,

(CASE Tbl_DocumentHeaders.Status2

WHEN '1' THEN 'Atverts'

WHEN '2' THEN 'Aprekinats'

WHEN '3' THEN 'Nodots'

WHEN '4' THEN 'Iegramatots' END) AS Status,

Tbl_DocumentHeaders."Route ID" AS Route,

Tbl_DocumentHeaders."Posting Date" AS Posting_Date,

Tbl_Fixed_Assets."FA Barcode" AS Barcode,

Tbl_Fixed_Assets."Blocked" AS Carantine

//FROM and JOIN

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS Tbl_DocumentRows

//Join connect to Table Clases in Navision

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$FA Class" AS Tbl_FACLASS

ON Tbl_DocumentRows."FA Class Code" = Tbl_FACLASS.Code

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$Document Headers" AS Tbl_DocumentHeaders

ON Tbl_DocumentHeaders."Document No_" = Tbl_DocumentRows."Document No_"

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$Fixed Asset" AS Tbl_Fixed_Assets

ON Tbl_DocumentRows."No_" = Tbl_Fixed_Assets."No_"

WHERE

Tbl_DocumentHeaders."Posting Date" > '2015-11-30 00:00:00.000'

AND

Tbl_FACLASS."Act FA No_ fill type" = 1

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 14

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 16

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 15

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 5

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 3

AND

Tbl_DocumentHeaders.Status2 = 4

GROUP BY

Tbl_DocumentRows."Document No_",

Tbl_DocumentHeaders."New FA Location Code",

Tbl_DocumentHeaders."FA Location Code",

Tbl_DocumentHeaders."Posting Date",

Tbl_FACLASS.Code,

Tbl_DocumentHeaders."Doc_ Type",

Tbl_DocumentHeaders."Doc_ Reason",

Tbl_DocumentHeaders.Status2,

Tbl_DocumentHeaders."Route ID",

Tbl_Fixed_Assets."FA Barcode",

Tbl_Fixed_Assets."Blocked"

;