Grouping data in Qlikview

[#1]

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

[MarcoWedel]

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

[#3]

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.

1. LOAD RecNo() as QuotID, 
2.      Money(Round(Pow(10,Rand()*6.2),0.01)) as value, 
3.      Ceil(Rand()*120) as items 
4. 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

[MarcoWedel]

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.

1. LOAD RecNo() as QuotID,
2.      Money(Round(Pow(10,Rand()*6.2),0.01)) as value,
3.      Ceil(Rand()*120) as items
4. 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

[#5]

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?

[Anonymous]

Hi Gerald,

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

Regards

Neetha

[#7]

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;

[Anonymous]

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

[#9]

Thanks Neetha,

looks much better yes. appreciate the help.

[Anonymous]

You're Welcome

[#11]

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;