topic Re: Need help with Calculated Dimension in Straight Table in QlikView

Need help with Calculated Dimension in Straight Table

Tue, 24 Jan 2017 01:18:30 GMT

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,

Re: Need help with Calculated Dimension in Straight Table

sunny_talwar — Tue, 24 Jan 2017 01:38:43 GMT

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

Re: Need help with Calculated Dimension in Straight Table

Tue, 24 Jan 2017 03:03:58 GMT

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.

Re: Need help with Calculated Dimension in Straight Table

Tue, 24 Jan 2017 03:06:15 GMT

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?

Re: Need help with Calculated Dimension in Straight Table

sunny_talwar — Tue, 24 Jan 2017 03:20:30 GMT

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)

Re: Need help with Calculated Dimension in Straight Table

Tue, 24 Jan 2017 03:47:07 GMT

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!

Re: Need help with Calculated Dimension in Straight Table

sunny_talwar — Tue, 24 Jan 2017 10:15:05 GMT

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

Re: Need help with Calculated Dimension in Straight Table

Fri, 27 Jan 2017 14:29:38 GMT

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.

Re: Need help with Calculated Dimension in Straight Table

jayanttibhe — Mon, 30 Jan 2017 00:53:08 GMT

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