Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
Hi David,
Are you looking for something like this?
If so the have attached the sample file.
Thanks,
Partha K
Yes Jaime,
This is exactly what I was looking for. Thank you.
Partha,
Thank you for putting this together. This is spot on. Much appreciated!
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!
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