Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 *;
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
Thanks but it didn't work.
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 *;
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
Good spot Jonathan, I completely overlooked that in an assumption it was the same
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.
replace " with [ and ]
Or try Decode(Len("Variant Code"),4,Right("Variant Code",2),Right("Variant Code",1))
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