Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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);

View solution in original post

12 Replies
Gysbert_Wassenaar

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
swuehl
MVP
MVP

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

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

Not applicable
Author

Thanks. I tried and I need dynamically show the results

rbecher
MVP
MVP

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

Astrato.io Head of R&D
swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

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);