Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrchristopher
Contributor III
Contributor III

Using conditions in the script to create groups

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.

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

5 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
mrchristopher
Contributor III
Contributor III
Author

Excellent soloution, Thank You.

Miguel_Angel_Baeyens

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
mrchristopher
Contributor III
Contributor III
Author

Both solutions worked well so far, the alternative code has helped me get my head around some of the scripting.

Thank you very much.