Turning an straight table expression into a dimension

[dawgfather]

I have an expression in a straight table chart that works once my data set and tables are loaded and gives me what I need and want, but I cannot seem use the outcome as a Dimension in a bar/column chart. If I try to use the same expression in a load statement, it cannot seem to see or reference the other previously loaded table – tells me it doesn’t exist. If I try it in a Calculated Dimension it will not work – gives an error in calculated dimension.

I essentially have a set of data showing me items and then the project(s) they are tied to. And some have multiple memberships. My idea is (and it works as an expression) to load that table into a reference table and then on the main table, as it loads and runs across the Item name, it will look to the reference table, count the number of (distinct) Projects that it sees for that Item, and then if it’s more than 1, then it fills in a “Yes” in the Multiple_YN field, otherwise it puts a “No”

 
How can I implement this expression into the load statement and make it work?

Sample:

Load

Table1: (reference table)

                Item {linked field}

                Project

                ...

{end}

And you might end up with stuff like:

Item                      Project

Chair                      Sitting

Sofa                       Sitting

Sofa                       Sleeping

Bed                        Sleeping

And if you counted “Sofa” then you would get > 1 since it is part of 2 “projects” - Sitting and Sleeping for example.

IF( (Count (DISTINCT Table1.Project)>1,'Yes','No')

-----------------------------------------------

Load

Table2:

Item,

Cost,

Color,

Location,

IF( (Count (DISTINCT Table1.Project)>1,'Yes','No') as Multiple_YN            (what I am trying to do)

{end}

Might end with something like this (this is what I get when I make a straight table):

Item      Cost       Color     Location               Multiple_YN

Bed        $100       White    Warehouse        No

Sofa       $150       Brown   Warehouse        Yes

Chair      $50         Silver     Warehouse        No

I would like to have that Multiple_YN as a Dimension to then count how many items are members of multiple projects, but I cannot get it to work.

Ideas?

[rwunderlich · Accepted solution]

As a Calculated Dim:

=aggr(if(Count(Distinct Project)>1, 'Yes', 'No'), Item)

As a scripted field:

Table2:

LOAD

*,

if(ProjectCount > 1, 'Yes', 'No') as Multiple_YN

;

LOAD

Item,

count(DISTINCT Project) as ProjectCount

Resident Table1

Group By Item

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[dawgfather]

OK, so I got the calculated dimension working, awesome - thank you.

Having some troubles with the scripting piece. Maybe it's my understanding of what goes where.

So if I have it right I would load Table1 as normal...

Table1:

Load

*,

From wherever

;

Then is it that I would do a load within a load somehow or would it be:

Table2:

Load

*,

if(ProjectCount > 1, 'Yes', 'No') as Multiple_YN,

From wherever

;

Load

Item,

Count(Distinct Project) as ProjectCount

Resident Table1

Group by Item

;

Or is it that the Load...from Resident Table 1 is somewhere within the Table 2 load statement?

[rwunderlich]

The table2 Load statement uses a preceding load. There is no "From wherever". the first load statement gets it's input from the second load statement -- which get's it's input from Resident Table1. It's executed from the bottom up.

Simplify with Preceding Load | Qlikview Cookbook

-Rob

[dawgfather]

Ok, so here's what I have going on. I load a SQL query into a QVD and then load that QVD into an overall master reference table. I then build other tables from that master table. So here's what I have in total:

QVD loads to MasterTable1

Table1:

Load

   Item,

   Project,

   LastUpdated

Resident MasterTable1;

Table2:

Load

   Item,

   Cost,

   Color,

   Location,

   Project,

   LastUpdated

Resident MasterTable1;

I have tried adding the if(ProjectCount > 1, 'Yes', 'No') as Multiple_YN to Table2 with a separate load after Table 2

Load

   Item,

   Count(Distinct Project) as ProjectCount

Resident Table1

Group by Item;

...but I keep getting errors like it can't find the ProjectCount field in the Table2 load portion. I tried putting the ProjectCount load table between Table1 and Table 2 and still didn't work. Might not really be an option. But at least I have the calculated field so I'll go ahead and mark that as complete.