Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create a new field within the script so that I can group data within Qlikview.
The condition is based on the 1st letter of the field 'Product Class' e.g. All product classes equal to 'A' = 'Group A' and everything else = 'Other'
Here is what I have so far:
ODBC CONNECT TO SysproCompanyA;
SQL SELECT Area,
CostValue,
Customer,
CustomerClass,
NetSalesValue,
ProductClass,
QtyInvoiced,
Salesperson,
StockCode,
TrnMonth,
TrnYear,
FROM SysproCompanyA.dbo.ArTrnDetail;
SELECT ProductClass, (Left(ProductClass,1)) AS Sector FROM SysproCompanyA.dbo.ArTrnDetail;
I've tried various If statments but I can't get any to work. Can you help?
BTW this is my 1st day of looking at any SQL scripting, so go easy.
Hi,
Instead of creating group in script you can create groups in document.
Click on Settings-> Document Properties -> Groups
Here you can create groups as per you want.
Hope this will help you.
Regards,
Kaushik Solanki
Hi,
Instead of creating group in script you can create groups in document.
Click on Settings-> Document Properties -> Groups
Here you can create groups as per you want.
Hope this will help you.
Regards,
Kaushik Solanki
Excellent soloution, Thank You.
Hello Kaushik,
That Groups tab is used to create drill down groups and cyclic groups based on existing fields (or even using expressions) but it doesn't seem to be the case.
Use the following code instead
ODBC CONNECT TO SysproCompanyA;
Table: // table label, not mandatory but very useful
LOAD Area, // the LOAD part is not mandatory either, but it's very recommended since it allows you to create new fields
Area,
CostValue,
Customer,
CustomerClass,
NetSalesValue,
ProductClass,
QtyInvoiced,
Salesperson,
StockCode,
TrnMonth,
TrnYear,
If(Left(ProductClass, 1) = 'A', 'Group A', 'Other') AS ProductGroup // this will create a new field based on the value existing in ProductClass
;
SQL SELECT Area,
CostValue,
Customer,
CustomerClass,
NetSalesValue,
ProductClass,
QtyInvoiced,
Salesperson,
StockCode,
TrnMonth,
TrnYear
FROM SysproCompanyA.dbo.ArTrnDetail;
Hope this helps.
BI Consultant
Hi Miguel,
You are right sir, but mrchristopher said that he is not good at sql. And he wanted to group the data.
Thus i have suggested him that way.
Regards,
Kaushik Solanki
Both solutions worked well so far, the alternative code has helped me get my head around some of the scripting.
Thank you very much.