Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I am running at the moment in really weird problem and can not come up with any conclusions.
My Data looks like that:
Container-Name | Action-Date | Step-Name |
---|---|---|
Container12 | 01.01.2016 | StepName-1 |
Container12 | 02.01.2016 | StepName-2 |
Container12 | 03.01.2016 | StepName-3 |
Container12 | 04.01.2016 | StepName-1-V |
Container43 | 01.01.2016 | StepName-A |
Container43 | 01.01.2016 | StepName-B |
I want to have table with the max step name the container past in time
So my result should look like that:
Container-Name | Action-Date | Step-Name |
---|---|---|
Container12 | 04.01.2016 | StepName-1-V |
Container43 | 01.01.2016 | StepName-B |
I set up a table with Container-Name as dimension and AGGR(max(Action-Date), Container-Name) as second dimension. If I now add the fact MaxString(Step-Name) the result looks for max string in all containers and not with the maximum date.
So my result looks like that:
Container-Name | Action-Date | Step-Name |
---|---|---|
Container12 | 04.01.2016 | StepName-3 |
Container43 | 01.01.2016 | StepName-B |
It seams like the fact column would ignore the two dimensions before hand?
Why?? Do I have a wrong understanding of dimensions and facts?
When I tried to solve the problem with set analysis and only the container as dimension:
maxstring({$<Action-Date= {'&(=max(Action-Date))'}>}StepName)
My result where right but just for one Container, so Qlik took the max-Date from all dates, ignoring the dimension again.
Container-Name | Step-Name | |
---|---|---|
Container12 |
| |
Container43 | - |
I also tried to combine the set analysis with AGGR but the result is still wrong.
Is there something I really miss or does Qlik have a problem?
I am thank full for any help.
Regards!
May be this:
Dimension
Container_Name
Expression
Date(Max(Action-Date))
FirstedSortedValue(Step_Name, -(Action-Date + (ID/1E10)))
Try using 1 dimension and 2 expressions:
Dimension
Container_Name
Expression
Date(Max(Action-Date))
FirstedSortedValue(Step_Name, -Action-Date)
Update: Also checkout how FirstSortedValue() function work
Value Associated with Min/Max Value of Another Field (Front End Solution)
This works if the container passed one Step-Name but if not the result is empty even so the date is unique
You can have multiple Step_Names for each date and container?
Yeah I have multiple rows, that is the point i didn't realized!
It is possible to get the first sorted value ordered by 2 parameters, or do I have to use a set analysis?
Would you want to show all the steps for the highest date within a container or pick one of the steps from the list? If it is the later, how do you plan to pick one? MaxString(Step Name)?
Container-Name | Action-Date | Step-Name | ID |
---|---|---|---|
Container12 | 01.01.2016 | StepName-1 | 1 |
Container12 | 02.01.2016 | StepName-2 | 2 |
Container12 | 03.01.2016 | StepName-3 | 3 |
Container12 | 04.01.2016 | StepName-1-V | 8 |
Container43 | 01.01.2016 | StepName-A | 05 |
Container43 | 01.01.2016 | StepName-B | 06 |
Container12 | 05.01.2016 | StepName-1-C | 4 |
Container12 | 05.01.2016 | StepName-1-D | 5 |
I want to show one container step with the highest date and id. But the id is not in the right order, it is just relevant if i have more steps in the same time.
So my results should look like that:
Container-Name | Action-Date | Step-Name |
---|---|---|
Container43 | 01.01.2016 | StepName-B |
Container12 | 05.01.2016 | StepName-1-D |
May be this:
Dimension
Container_Name
Expression
Date(Max(Action-Date))
FirstedSortedValue(Step_Name, -(Action-Date + (ID/1E10)))
Thank You.
Great and fast response
Hi Sunny,
Could you pl help to understand the 1E10 divisor, didn't get that. Thanks in advance.
Digvijay,