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

[Anonymous]

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;

[prma7799]

Sorting Group into Subgroups

[MarcoWedel]

Hi,

one solution could be:

Custom Filter(unusual range)

hope this helps

regards

Marco

[MarcoWedel]

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;

[engishfaque]

Dear Bastian,

Kindly find attached documents.

Kind regards,

Ishfaque Ahmed

[Anonymous]

Hi,

see the link:

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

Hope it helps.

Regards

Neetha

[#8]

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

[Anonymous]

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

[gartigas]

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