Limiting Dimension Data by String Length

[#1]

Hi all,

Just started using Qlik today, and I'm trying to display some of my data on a line chart, where the Date is on the X-Axis and Project Budget is on the Y-Axis. 

The data set contains a list of project numbers and associated balances. A project ID is represented by a string of numbers that looks like XXXXX.YY.ZZZ, where each level has its own balance.  The data set is updated via concatenation each day.

See below:

Date	Project Id	Balance Available
4/10/17	12345	100
4/10/17	12345.01	100
4/10/17	12345.01.101	70
4/10/17	12345.01.102	30
4/10/17	54321	200
4/10/17	54321.01	200
4/10/17	54321.01.101	50
4/10/17	54321.01.102	150
4/11/17	12345	80
4/11/17	12345.01	80
4/11/17	12345.01.101	60
4/11/17	12345.01.102	20
4/11/17	54321	190
4/11/17	54321.01	190
4/11/17	54321.01.101	50
4/11/17	54321.01.102	140

My data set has roughly 100 unique Project Id numbers per day.  I would only like to graph the parent Project Id number, such that I would see the balances for 12345 and 54321 on the line chart by day.

My Line Chart Dimensions are [Date] and [Project Id], and my Measure is [Balance Available].

I'm looking to implement something that mimics either a "Select * From [Project Id] Where [Project Id].length = 5" or "Select * From [Project Id] Where ![Project Id].Contains ".""

How do I limit the Line Chart [Project ID] Dimension to show only parent [Project Id]s? 

Thank you,

David

[jaimeaguilar · Accepted solution]

Hi,

there is also a length function in QlikView. It is called Len. You can create a new field, something like this: if(len([Project Id]) = 5, [Project Id]) as NewField. I'm still not sure if this exactly what you need/want, but you may give it a try,

best regards

[ogster1974]

I assume over time that the project id can and will change in length so would suggest you create a flag to Id the parent project.  something like

If (WildMatch([product id],'*.*') = 0,'Y','N') AS ParentProject

In the load script.

[parthakk]

Hi David,

Are you looking for something like this?

If so the have attached the sample file.

Thanks,

Partha K

[#5]

Yes Jaime,

This is exactly what I was looking for.  Thank you.

[#6]

Partha,

Thank you for putting this together.  This is spot on. Much appreciated!

[#7]

Andy, this is a more implementation agnostic approach.  While it is unlikely that project numbers will exceed 5 digits in the foreseeable future, this is a better approach for a data set that is subject to change.  Thank you!

[ogster1974]

As an additional observation as to how you might implement it.

If you go with the Calculated Dimension approach over the create a flag/ParentProject Field in the Load script that will put more work on the client side to generate especially if this Dimension is used alot so making t=your dashboard run slower for users.

Regards

Andy