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)
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!
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 :
LOAD Upper(Manufacturer) as Manufacturer,
LOAD [CO2 Bottom],
Band as BandAlt,
(ooxml, embedded labels, table is Sheet1);
Left Join (TaxBands)
IntervalMatch ([CO2 g/km]) load [CO2 Bottom], [CO2 Top] resident TaxBands;
or with a nested if() like you already assumed:
IF(value<10000, ' <10k €',
IF(value<30000, ' <30k €',
IF(value<100000, ' <100k €',
))) as ValueClassification
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!
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!!
I would recomend also, a nested if in yor load script:
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
regards--- good luck