Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Missing Dimension values

Hi Experts,

I have data like below:

   

IDGradeScore
11273
21275
31279
41311
51325
61330
71332
81333
91345
101346
111347
121348
131349

   

GradeLowHigh
1250360
2265400

As the qvf file attached, I can build a bar chart,

However, there are two things I want to have here:

1. I don't want to miss any score range even it is 0 count, such as 280-300

2. I need the all the score ranges from Low to High(Data should read from LowHigh table at run time) with interval 20 in my bar chart:

   

Score RangeIDCount
250-2690
270-2893
290-3090
310-3292
330-3498
350-3600

How can I achieve these?

Thanks for you help in advance!

Jing

1 Solution

Accepted Solutions
MVP
MVP

Re: Missing Dimension values

I've added Content to the key for mapping. I've also limited the upper bound for the buckets.

LowHigh:

LOAD [Content],[Grade], BucketLow, BucketHigh, BucketLow & ' - ' & BucketHigh as Bucket;

LOAD [Content],

  [Grade],

   [Low]+(iterno()-1)*20 as BucketLow,

      RangeMin([Low]+(iterno())*20-1,High) as BucketHigh,

   [Low],

   [High]

FROM [lib://ACCRBottomAnalysis/sample1.xlsx]

(ooxml, embedded labels, table is Sheet2)

WHILE [Low]+(iterno()-1)*20 <= [High];

MAP:

MAPPING LOAD

    [Content]&'-'&Grade&'-'& (BucketLow+iterno()-1), Bucket

Resident LowHigh

WHILE BucketLow+iterno()-1 <= BucketHigh;

Score:

LOAD [ID],

  [Content],

  [Grade],

  [Score],

    ApplyMap('MAP', [Content]&'-'&[Grade]&'-'&Score) as Bucket

FROM [lib://ACCRBottomAnalysis/sample1.xlsx]

(ooxml, embedded labels, table is Sheet1);

12 Replies

Re: Missing Dimension values

The class function doesn't create the 'missing' range if there's no data. You don't have score values in that range so the range cannot be created. If you want that range despite that then you need to add dummy data so at least one score value in that range exists in your data.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Missing Dimension values

You can try creating the complete range of buckets in your script from your LowHigh table as a dimensional table, then map your Scores to the buckets using ApplyMap().

The mapping could be done like described here:

creating an age group from a date field

I can't easily modify & reload your script, so if you need any further help, it would be good if you could post the excel source data.

Stefan

MVP
MVP

Re: Missing Dimension values

You could use ValueLoop() although this is not very dynamic:

Dimension:

ValueLoop(260, 340, 20)

Measure:

Sum(If(Score >= ValueLoop(260, 340, 20) AND Score < ValueLoop(260, 340, 20) +20, 1, 0))

Not applicable

Re: Missing Dimension values

thanks Swuehl! I have attached my source data. Please use sheet1 and sheet2

Not applicable

Re: Missing Dimension values

Thanks. I tried and I need dynamically show the results

MVP
MVP

Re: Missing Dimension values

You could use variables to set min/max dynamically..

MVP
MVP

Re: Missing Dimension values

Have a look at attached sample.

Your buckets seem to be depending on grade, so I assume you are using the graph with a single grade selected?

Not applicable

Re: Missing Dimension values

Thank you so much swuehl! This almost solve my puzzle though the actual data is more complicated. I will have at least one more selection for my bucket, not only grade. I added it in attached new qvf. Could you please check if the data load script is correct? I guess I need to learn more about ApplyMap.

One more thing I want is that the last bucket should just go up to High not exceed the High, ex. 385-400, not 385-404 to follow the 20 interval. How can I limit it?

I also attached my new data sheet.

Thanks,

Jing

MVP
MVP

Re: Missing Dimension values

I've added Content to the key for mapping. I've also limited the upper bound for the buckets.

LowHigh:

LOAD [Content],[Grade], BucketLow, BucketHigh, BucketLow & ' - ' & BucketHigh as Bucket;

LOAD [Content],

  [Grade],

   [Low]+(iterno()-1)*20 as BucketLow,

      RangeMin([Low]+(iterno())*20-1,High) as BucketHigh,

   [Low],

   [High]

FROM [lib://ACCRBottomAnalysis/sample1.xlsx]

(ooxml, embedded labels, table is Sheet2)

WHILE [Low]+(iterno()-1)*20 <= [High];

MAP:

MAPPING LOAD

    [Content]&'-'&Grade&'-'& (BucketLow+iterno()-1), Bucket

Resident LowHigh

WHILE BucketLow+iterno()-1 <= BucketHigh;

Score:

LOAD [ID],

  [Content],

  [Grade],

  [Score],

    ApplyMap('MAP', [Content]&'-'&[Grade]&'-'&Score) as Bucket

FROM [lib://ACCRBottomAnalysis/sample1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Community Browser