topic Re: MSSQL Group by Function in QlikView

MSSQL Group by Function

Tue, 14 Aug 2012 08:55:11 GMT

Hi,

I am new in qlikview, i tried googling before I do this first post so I hope someone can help me.

I need to Load the following MS-SQL script in QlikView.

Thank you.

SELECT

max([Item No_]) as 'Item No_'

,sum([Quantity]) as 'Quantity'

,max(left([Variant Code],2)) as 'Color'

,max(right([Variant Code],len([Variant Code])-2)) as 'Size'

FROM Ledger_Entry

group by [Item No_], right([Variant Code],len([Variant Code])-2)

having sum([Quantity]) > 0

result:

21143752	7	04	S
21143752	2	04	L
21143752	9	04	LL

I tried this one but i have some errors (please see my attachment):

Item_Ledger:

QUALIFY *;

UNQUALIFY "Item No_";

LOAD

"Item No_",

"Quantity",

"Color",

"Size";

SQL SELECT

max("Item No_") as "Item No_"

,sum(Quantity) as "Quantity"

,max(left("Variant Code",2)) "Color"

,max(right("Variant Code",len("Variant Code")-2)) as "Size"

FROM Ledger_Entry

group by "Item No_", max(right("Variant Code",len("Variant Code")-2))

having "Quantity">0;

UNQUALIFY *;

Re: MSSQL Group by Function

Tue, 14 Aug 2012 09:02:45 GMT

try

QUALIFY *;

UNQUALIFY "Item No_";

Item_Ledger:

LOAD Item_No_,

Quantity,

Color,

Size;

SQL SELECT

max("Item No_") as "Item_No_"

,sum(Quantity) as "Quantity"

,max(left("Variant Code",2)) "Color"

,max(right("Variant Code",len("Variant Code")-2)) as "Size"

FROM Ledger_Entry

group by "Item No_", max(right("Variant Code",len("Variant Code")-2))

having "Quantity">0;

UNQUALIFY *;

Your table name was in the wrong place

Re: MSSQL Group by Function

Tue, 14 Aug 2012 09:10:16 GMT

Thanks but it didn't work.

Re: MSSQL Group by Function

Tue, 14 Aug 2012 09:23:01 GMT

I forgot to remove the " try

QUALIFY *;

UNQUALIFY Item_No_;

Item_Ledger:

LOAD Item_No_,

Quantity,

Color,

Size;

SQL SELECT

max("Item No_") as "Item_No_"

,sum(Quantity) as "Quantity"

,max(left("Variant Code",2)) "Color"

,max(right("Variant Code",len("Variant Code")-2)) as "Size"

FROM Ledger_Entry

group by "Item No_", max(right("Variant Code",len("Variant Code")-2))

having "Quantity">0;

UNQUALIFY *;

Re: MSSQL Group by Function

jonathandienst — Tue, 14 Aug 2012 09:27:50 GMT

Variant Code is in an aggregation expression. You should not need it in the group by clause.

Can you not just group by [Item No_]?

If not, at least remove the "max" in the group by:

group by "Item No_", right("Variant Code",len("Variant Code")-2)

In essence, the SQL keyword passes everything following to the server. If it runs on the server, then the same statement will run in QV. But your QV SQL statement is not the same as the MSSQL statement in your post.

Regards

Jonathan

Re: MSSQL Group by Function

Tue, 14 Aug 2012 09:33:32 GMT

Good spot Jonathan, I completely overlooked that in an assumption it was the same

Re: MSSQL Group by Function

Tue, 14 Aug 2012 10:02:09 GMT

Hi Jonathan,

Thanks, you solved the script error, except for one thing:

error: Invalid length parameter passed to the RIGHT function.

max(right("Variant Code",len("Variant Code")-2)) as 'Size'

so I remove the len("Variant Code")-2 and replace with 2: (and it works)

max(right("Variant Code",2)) as 'Size'

But I really need to have this > len("Variant Code")-2 because:

Variant Code:

04S

04M

04L

04XL

There is always a 2 digit (color code) before the size (S, M, L, XL)

so this: max(right("Variant Code",2)) as 'Size' will give me: (4S, 4M, 04L, XL) which is wrong.

Re: MSSQL Group by Function

Tue, 14 Aug 2012 10:06:24 GMT

replace " with [ and ]

Re: MSSQL Group by Function

Tue, 14 Aug 2012 10:10:06 GMT

Or try Decode(Len("Variant Code"),4,Right("Variant Code",2),Right("Variant Code",1))

Re: MSSQL Group by Function

jonathandienst — Tue, 14 Aug 2012 11:38:44 GMT

The error is probably due to values that are only 2 characters or less in Variant Code, so you are passing in 0 or a negative number into the Right function.

Regards

Jonathan

Re: MSSQL Group by Function

jonathandienst — Tue, 14 Aug 2012 11:40:33 GMT

If that is the case, try this:

max(right(' ' & "Variant Code",len("Variant Code")-2))

Regards

Jonathan

Re: MSSQL Group by Function

Wed, 15 Aug 2012 02:07:29 GMT

Thank everyone for the effort.

Hi Felim,

I tried your decode and [] but didn't work.

Hi Jonathan,

I tried max(right(' ' & "Variant Code",len("Variant Code")-2))

but i have this error: The data types varchar and varchar are incompatible in the boolean AND operator.

Re: MSSQL Group by Function

Wed, 15 Aug 2012 02:50:36 GMT

Thanks everyone for the helpful solution. I found a workaround solution to my problem.

I separate the load by 2:

first load: Variant Code Where Len = 4 - so i can get: right("Variant Code",2)

Add load: Variant Code with Len = 3 - so i can get: right("Variant Code",1)

- with this, i can avoid using the Len Function on my script, an ingenious solution but ok for me.

Item_Ledger:

LOAD "Item No_",

Quantity,

"Color",

"Size";

SQL SELECT

max("Item No_") as "Item No_"

,sum(Quantity) as "Quantity"

,max(left("Variant Code",2)) "Color"

,max(right("Variant Code",2)) as "Size"

FROM (Select * From Ledger Entry Where len([Variant Code])=4) as len4

group by "Item No_", right("Variant Code",2)

having sum(Quantity)>0;

Add LOAD

"Item No_",

Quantity,

"Color",

"Size";

SQL SELECT

max("Item No_") as "Item No_"

,sum(Quantity) as "Quantity"

,max(left("Variant Code",2)) "Color"

,max(right("Variant Code",1)) as "Size"

FROM (Select * From Ledger_Entry Where len([Variant Code])=3) as len3

group by "Item No_", right("Variant Code",1)

having sum(Quantity)>0;

Re: MSSQL Group by Function

jonathandienst — Wed, 15 Aug 2012 06:20:46 GMT

Sorry - forgot that this was running in SQL. Should be

max(right(' ' + "Variant Code",len("Variant Code")-2))

Re: MSSQL Group by Function

Wed, 15 Aug 2012 06:34:46 GMT

Hi Jonathan,

I still have the same error. I guess add load will do the trick for now.

Thanks a million.

Resty