Need help with Calculated Dimension in Straight Ta... - Qlik Community

Report Inappropriate Content · ‎2017-01-23

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;

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,

sunny_talwar · ‎2017-01-23

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

Report Inappropriate Content · ‎2017-01-23

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.

Report Inappropriate Content · ‎2017-01-23

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 · ‎2017-01-23

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)

Report Inappropriate Content · ‎2017-01-23

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 · ‎2017-01-24

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

Report Inappropriate Content · ‎2017-01-27

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 · ‎2017-01-29

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

Need help with Calculated Dimension in Straight Table