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

[jonathandienst]

Hi

If that is the case, try this:

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

Regards

Jonathan

[#3]

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.

[#4]

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;

[jonathandienst]

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

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

[#6]

Hi Jonathan,

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

Thanks a million.

Resty