topic Re: Grouping data in Qlikview in QlikView

Grouping data in Qlikview

Wed, 17 Dec 2014 10:55:59 GMT

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

Re: Grouping data in Qlikview

Anonymous — Wed, 17 Dec 2014 11:02:31 GMT

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;

Re: Grouping data in Qlikview

prma7799 — Wed, 17 Dec 2014 11:03:41 GMT

Sorting Group into Subgroups

Re: Grouping data in Qlikview

MarcoWedel — Wed, 17 Dec 2014 12:09:30 GMT

Hi,

one solution could be:

Custom Filter(unusual range)

hope this helps

regards

Marco

Re: Grouping data in Qlikview

MarcoWedel — Wed, 17 Dec 2014 12:16:45 GMT

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;

Re: Grouping data in Qlikview

engishfaque — Wed, 17 Dec 2014 12:21:57 GMT

Dear Bastian,

Kindly find attached documents.

Kind regards,

Ishfaque Ahmed

Re: Grouping data in Qlikview

Anonymous — Wed, 17 Dec 2014 12:30:56 GMT

Hi,

see the link:

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

Hope it helps.

Regards

Neetha

Re: Grouping data in Qlikview

Wed, 17 Dec 2014 15:12:37 GMT

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

Re: Grouping data in Qlikview

Anonymous — Wed, 17 Dec 2014 20:10:45 GMT

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:

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!!

Re: Grouping data in Qlikview

gartigas — Wed, 17 Dec 2014 20:39:21 GMT

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

Re: Grouping data in Qlikview

MarcoWedel — Wed, 17 Dec 2014 21:17:39 GMT

one example:

tabQuotations:
LOAD *,
     IF(value<10000, '<10k €',
      IF(value<30000, '<30k €',
       IF(value<100000, '<100k €',
        IF(value<1000000, '<1m €',
         IF(value>=1000000, '>=1m €','none'       
     ))))) as ValueClassification,
     IF(items<5, '<5 items',
      IF(items<20, '<20 items',
       IF(items<50, '<50 items',
        IF(items<100, '<100 items',
         IF(items>=100, '>=100 items','none'       
     ))))) as ItemAmountClassification;
LOAD RecNo() as QuotID,
     Money(Round(Pow(10,Rand()*6.2),0.01)) as value,
     Ceil(Rand()*120) as items
AutoGenerate 100;

To correct the order of the ValueClassification buckets, you could extend this solution using the Dual() function to something like:

tabQuotations:
LOAD *,
     IF(value<10000, Dual('<10k €',1),
      IF(value<30000, Dual('<30k €',10000),
       IF(value<100000, Dual('<100k €',30000),
        IF(value<1000000, Dual('<1m €',100000),
         IF(value>=1000000, Dual('>=1m €',1000000),Dual('none',0)      
     ))))) as ValueClassification,
     IF(items<5, Dual('<5 items',1),
      IF(items<20, Dual('<20 items',5),
       IF(items<50, Dual('<50 items',20),
        IF(items<100, Dual('<100 items',50),
         IF(items>=100, Dual('>=100 items',100),Dual('none',0)       
     ))))) as ItemAmountClassification;
LOAD RecNo() as QuotID,
     Money(Round(Pow(10,Rand()*6.2),0.01)) as value,
     Ceil(Rand()*120) as items
AutoGenerate 100;

so you don't have to care about special sorting expressions when using the classification fields:

hope this helps

regards

Marco

Re: Grouping data in Qlikview

Thu, 18 Dec 2014 11:23:07 GMT

Hello Marco,

That is quite the profound example you are giving me here, thanks again! Unfortunately I cannot open your example file as I am not yet working with a QV business license.

Would you be so kind and elaborate the following code for me? I saw the CEIL command before (as far as I remember, I found a code with CEIL to create yearquarters from my months). Besides, I have not the slightest idea what these command lines do for me resp. for my data.

LOAD RecNo() as QuotID,
Money(Round(Pow(10,Rand()*6.2),0.01)) as value,
Ceil(Rand()*120) as items
AutoGenerate 100;

And what about your suggestion concerning the sorting with the help of the DUAL function? I assume the second value in the command just makes sure, that the classes are in the correct order when creating a list box with them? Is it necessary to use the values 1, 10.000, 30.000, 100.000, 1.000.000 because these values (all but the first one) define my desired classes? Or could you also use 1, 2, 3, 4, 5? Just trying to understand the syntax and sense here...

Regards,

Bastian

Re: Grouping data in Qlikview

MarcoWedel — Thu, 18 Dec 2014 12:06:34 GMT

Hi,

this code creates random test data, since I didn't have your's to test on. You would not use this part together with your live data.

LOAD RecNo() as QuotID,
Money(Round(Pow(10,Rand()*6.2),0.01)) as value,
Ceil(Rand()*120) as items
AutoGenerate 100;

The ceil rounds a value up to the next integer number, I used it to delete the decimal places the Rand() function delivers (just to get test data that is easier to read).

The dual() function is the interesting bit here, because it generates text values with underlying numerical values, i.e. you can use them like any other numerical field for calculations or to sort numerical.

hope this helps

regards

Marco

Re: Grouping data in Qlikview

Mon, 02 Feb 2015 11:42:04 GMT

Hi John,

If I import a list of Claim statuses, and would like to group certain status's, how would I do that? same as you example above?

Re: Grouping data in Qlikview

Anonymous — Wed, 04 Feb 2015 16:38:16 GMT

Hi Gerald,

Please upload sample data in excel,so will try to help.

Regards

Neetha

Re: Grouping data in Qlikview

Thu, 05 Feb 2015 06:28:47 GMT

Hi Neetha,

Thank you. I did come right. here is what I did.

if(([Claim Status])= 'Refer for Approval','Pre-Auth',
if(([Claim Status])= 'Assessor report received','Pre-Auth',
if(([Claim Status])= 'Claim Registered','Pre-Auth',
if(([Claim Status])= 'Assessor Appointed','Pre-Auth',
if(([Claim Status])= 'Contact customer','Pre-Auth',
if(([Claim Status])= 'First Notice of Loss','Pre-Auth',
if(([Claim Status])= 'Claim Registered (Repo)','Pre-Auth',
if(([Claim Status])= 'Refer for Approval (Repo)','Pre-Auth',
if(([Claim Status])= 'Concluding Term extension','Pre-Auth',
if(([Claim Status])= 'Decline Repair (Repo)','Pre-Auth',
if(([Claim Status])= 'Repudiated','Pre-Auth',
if(([Claim Status])= 'Decline repair','Pre-Auth',
if(([Claim Status])= 'Open','Pre-Auth',
if(([Claim Status])= 'Third party notification','Pre-Auth',
if(([Claim Status])= 'Attorney Appointed','Pre-Auth',
if(([Claim Status])= 'Tracer Appointed','Pre-Auth',
if(([Claim Status])= 'Re-Opened (Mandate Required)','Pre-Auth',
if(([Claim Status])= 'Repudiated (Payment to Supplier)','Pre-Auth',
if(([Claim Status])= 'Registered In Error','Not Taken up',
if(([Claim Status])= 'NTU/Cancelled','Not Taken up',
if(([Claim Status])= 'Cancelled','Not Taken up',
if(([Claim Status])= 'Not taken up','Not Taken up','Post-Auth'))))))))))))))))))))))
as Bucket;

Re: Grouping data in Qlikview

Anonymous — Thu, 05 Feb 2015 10:30:02 GMT

Hi Gerald,

Try:

Your expression is also right,but below will be more cleaner and neat:

if(match([Claim Status],'Refer for Approval','Assessor report received','Claim Registered','Assessor Appointed','Contact customer','First Notice of Loss',
'Claim Registered (Repo)','Refer for Approval (Repo)','Concluding Term extension','Decline Repair (Repo)','Repudiated', 'Decline repair', 'Open',
'Third party notification','Attorney Appointed','Tracer Appointed','Re-Opened (Mandate Required)','Repudiated (Payment to Supplier)'),'Pre-Auth',
if(match([Claim Status]= 'Registered In Error', 'NTU/Cancelled', 'Cancelled','Not taken up'),'Not Taken up','Post-Auth')) as Bucket;

Regards

Neetha

Re: Grouping data in Qlikview

Thu, 05 Feb 2015 11:13:42 GMT

Thanks Neetha,

looks much better yes. appreciate the help.

Re: Grouping data in Qlikview

Anonymous — Thu, 05 Feb 2015 11:16:38 GMT

You're Welcome

Re: Grouping data in Qlikview

Fri, 06 Feb 2015 12:20:18 GMT

Hi Neetha,

another grouping I want to do, involves a list of vehicle names. now there are a long list of Toyotas, some are people carries, and some are pick ups, which I want to group as "Bakkies". below is my scripting. it is not working.

what am I doing wrong here? Please also advise how can split the count of Toyota people carries and Toyota hilux's

I attached the list of all vehicles

LOAD *,
if(match([Motor Desc] like '*HILUX*','Bakkie'),
if(match([Motor Desc] like '*TOYOTA*','Toyota'),
if(match([Motor Desc] like '*VOLGSWAGEN*','*MERCEDES-BENZ*','*IVECO*'),'Long Distance',
if(match([Motor Desc] like '*NISSAN*','Nissan'),'Chinese/Other')))) as Grouping;