Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Grouping data in Qlikview

As my first question was answered so quickly be the community, I thought I might just try it again

I have data that comes from SAP and I use a report to extract the data (so far I work with the personal edition of Qlikview and we do not have any API for SAP in our company).

I currently pre-group the information I load into Qlikview in Excel, simply because I do not know how to group data in Qlikview.

I created various groupings.

ValueClassification (based on the value of an quotation)

value     <10k €

value     <30k €

value     <100k €

value     <1m €

value     >1m €

ItemAmountClassification (how many items contains a quotation)

<5 items

<20 items

<50 items

<100 items

>100 items

etc.

I created these classifications in Excel with IF, THEN commands. I know that the same is existing in QV, but I neither know the syntax, nor do I know how to create a new field in my data with that new information so that I can use it as list box or as dimension for a chart, etc. I assume I need to execute the command for the grouping in the script (CTRL+E)?

Can anyone recommend a good tutorial or explanation for the topic of creating classifications resp. sub-groups in a data set?

Thank in advance you for your time and feedback!

Best regards,

Bastian

22 Replies
Highlighted
Employee
Employee

Re: Grouping data in Qlikview

Hi Bastian,

You might want to look into IntervalMatch(). The documentation can be a little sketchy so I will show you an example. In my case I wanted to match CO2 emissions from a car database to the tax bands they fall into. I use a left join to remove some synthetic keys and make things cleaner :

Cars:

LOAD Upper(Manufacturer) as Manufacturer,
Description,
Model,

[CO2 g/km],

FROM
dvla_data.qvd
(
qvd);

TaxBands:
LOAD [CO2 Bottom],
[CO2 Top],
Band,
Band as BandAlt,
[1st year],
[Std rate]
FROM
TaxBands2012.xlsx
(
ooxml, embedded labels, table is Sheet1);

IntervalMatch:
Left Join (TaxBands)
IntervalMatch ([CO2 g/km]) load [CO2 Bottom], [CO2 Top] resident TaxBands;

Highlighted
Master III
Master III

Re: Grouping data in Qlikview

Highlighted

Re: Grouping data in Qlikview

Hi,

one solution could be:

Custom Filter(unusual range)

hope this helps

regards

Marco

Highlighted

Re: Grouping data in Qlikview

or with a nested if() like you already assumed:

LOAD fields,

          value,

          IF(value<10000, ' <10k €',

            IF(value<30000, ' <30k €',

              IF(value<100000, ' <100k €',

                  ...

           ))) as ValueClassification

FROM YourExcelOrReportSource;

Highlighted
Specialist III
Specialist III

Re: Grouping data in Qlikview

Dear Bastian,

Kindly find attached documents.

Kind regards,

Ishfaque Ahmed

Highlighted
Master
Master

Re: Grouping data in Qlikview

Hi,

see the link:

http://community.qlik.com/message/670747#670747

Hope it helps.

Regards

Neetha

Highlighted
Not applicable

Re: Grouping data in Qlikview

Everyone,

Thank you for your replies and suggested tutorials. I will take a look at all of them and try to create my subgroups. I encounter some problems within this process as not all my subgroups are based on plain numbers. I was only able to create some of them in Excel with the help of a vlookup and various if, then commands which take different data into consideration.

Maybe I need to ask again for a specific example.

Until then, thanks for the suggestions!

Bastian

Highlighted
Specialist
Specialist

Re: Grouping data in Qlikview

Hi Bastian I think there might be easier way to solve your problem. Here is my approach to it. I have an excel file similar to yur req. My script is below:

qlik1.PNG

qlik2.PNG

I am planning to create a pivot table where u can group by Item amount classification or value class... I am getting an error after execution. says bad syntax. Can an expert tell me whats wrong. here is my excel file(data source). Any pointers would be precious!!

qlik3.PNG

Highlighted
Contributor II
Contributor II

Re: Grouping data in Qlikview

I would recomend also, a nested if in yor load script:

LOAD

  Field1,

  Field2,

  ***************************

  if((value> 0) and (value <10000),'0-10k',

  if(((value> 10000) and (value < 30000)),'10k-30k',

  if(((value> 30000) and (value < 100000)),'30k-100k',

  if(((value> 100000) and (value < 1000000)),'100k-1M',

  if(((value> 1M),'> 1M',

  'Other' ))))) as Value_Range

  *********************************

FROM yourTable;

regards--- good luck

gustavo