Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Limiting Dimension Data by String Length

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:

DateProject IdBalance Available
4/10/1712345100
4/10/1712345.01100
4/10/1712345.01.10170
4/10/1712345.01.10230
4/10/1754321200
4/10/1754321.01200
4/10/1754321.01.10150
4/10/1754321.01.102150
4/11/171234580
4/11/1712345.0180
4/11/1712345.01.10160
4/11/1712345.01.10220
4/11/1754321190
4/11/1754321.01190
4/11/1754321.01.10150
4/11/1754321.01.102140

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

1 Solution

Accepted Solutions
jaimeaguilar
Valued Contributor II

Re: Limiting Dimension Data by String Length

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

7 Replies
jaimeaguilar
Valued Contributor II

Re: Limiting Dimension Data by String Length

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
Honored Contributor II

Re: Limiting Dimension Data by String Length

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
Contributor II

Re: Limiting Dimension Data by String Length

Hi David,

Are you looking for something like this?

If so the have attached the sample file.

Thanks,

Partha K

Not applicable

Re: Limiting Dimension Data by String Length

Yes Jaime,

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

Not applicable

Re: Limiting Dimension Data by String Length

Partha,

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

Not applicable

Re: Limiting Dimension Data by String Length

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
Honored Contributor II

Re: Limiting Dimension Data by String Length

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