MSSQL Group by Function

[#1]

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 *;

[#2]

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

[#3]

Thanks but it didn't work.

[#4]

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 *;

[jonathandienst]

Hi

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

[#6]

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

[#7]

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.

[#8]

replace " with [ and ]

[#9]

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

[jonathandienst]

Hi

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