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

Need help with Calculated Dimension in Straight Table

I am not sure if what I am doing is possible.  It is also somewhat difficult to fully articulate but let me try to simplify it.

I should start by saying, I'm trying to build a Straight Table to show a specific result.

I have a dataset with the following data structure

Policy     BaseCode     Type A$     Type B$     Type C$     Type D$

1               105               $100               $10          $5            $0

2               201               $500               $200        $0            $50

I then have a lookup table with the following information

BaseCode          Type          Code

105                      A              ABC

105                      B              XYZ

105                      C              DEF

105                      D              JKL

201                      A              QRS

201                      B              XYZ

201                      C              MNO

201                      D              JKL

The combination of BaseCode, Type, and Code are not necessarily unique, but the combination of all the Type and Code possibilities to each BaseCode is unique.  That's not necessarily relevant.

What I want to be able to show from my original table above is this;

Policy | BaseCode | Type A Code | Type B Code | Type C Code | Type D Code | Type A$ | Type B$ | Type C$

1        |   105         |        ABC       |      XYZ        |       DEF        |                     |   $100    |   $10      |    $5   

2        |   201         |       QRS       |      XYZ        |                      |      JKL         |    $500   |    $200   |     $0    

(edit: I removed Type D $ because the formatting on the website was getting messed up)

I'm really struggling to figure out how to do this, because when I try to do a calculated dimension, it keeps duplicating the records (I need 1 line per Policy).  Not only that, but it's duplicating all the values so the duplicated results don't make any sense.

Is it possible to build this?  Again, I'm looking to do it in a straight table without having to do anything to the data itself.

Thanks,

8 Replies
sunny_talwar

Not sure if this is perfect and will work with your data or not, but may be this?

Capture.PNG

Not applicable
Author

Unfortunately that didn't work for me.  Is it possible I would need to aggregate by more dimensions?  I assumed I shouldn't need to.  I have a lot of dimensions in my table though so not sure.

This is what I have;

From my example above, these are the changes for my actual dataset;

BaseCode = BaseCodeID

Type = Section

Code = VB Section Code

Type A$ = Section A Reserve

Aggr(if(RowNO()=1 and [Section A Reserve] > 0, [VB Section Code]),BaseCodeID, Section)

Am I doing anything wrong with this formula?

Thanks for the help.

Not applicable
Author

Actually I think the issue is likely I oversimplified my actual data.

My QVW is built primarily off of two Data Tables, with about 30 lookup tables.

One of the data tables is 1 row per policy.  However, the second Fact Table is anywhere from 1 to 50 rows per policy.  So is it possible that is the issue?

sunny_talwar

I would try to replace Section with a field which is unique in your second table. Do you have such a field?

Aggr(if(RowNO()=1 and [Section A Reserve] > 0, [VB Section Code]),BaseCodeID, Section)

Not applicable
Author

At first pass that didn't work either unfortunately.

I'll keep playing with it though, hopefully I can figure it out from this, thanks!

sunny_talwar

Would you be able to share a small sample of data??

Not applicable
Author

I just created a very rough example of what my data looks like.  My actual data is confidential information, but this is a rough idea of what it is.

jayanttibhe
Creator III
Creator III

Just try to do everything in the script as mentioned in attached. I referred to the original post and sample data posted in it.