Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am facing problem to show the required record , below is my requirement
ID | Path | Total |
202 | 6 | 21 |
202 | 12 | 24 |
410 | 6 | 16 |
410 | 12 | 22 |
410 | 18 | 24 |
418 | 6 | 15 |
418 | 12 | 21 |
418 | 18 | 24 |
511 | 6 | 15 |
511 | 12 | 21 |
511 | 18 | 24 |
8443 | 6 | 12 |
8443 | 12 | 18 |
8443 | 18 | 24 |
92663 | 6 | 11 |
92663 | 12 | 17 |
92663 | 18 | 23 |
92663 | 24 | 24 |
I want to filter record based on the variable say vFilter.
1. If no value is passed in vFilter then below output is required -
ID | Path | Total |
202 | 12 | 24 |
410 | 18 | 24 |
418 | 18 | 24 |
511 | 18 | 24 |
8443 | 18 | 24 |
92663 | 24 | 24 |
2. If value is passed then accordingly dat needs to be filtered
say vFilter = 15 then below output is required
ID | Path | Total |
202 | 6 | 21 |
410 | 6 | 16 |
418 | 6 | 15 |
511 | 6 | 15 |
8443 | 12 | 18 |
92663 | 12 | 17 |
Can you please help me on this?
HI Shivesh,
Managed to get desired output,just extended your expression bit further and removed Path from the dimension
if(len(vSelect)=0 ,sum({<ID,Total1 = {"$(=max(Total1))"}>}Total1),FirstSortedValue ( {< ID,Total1 = {">=$(vSelect)"} >}Total1,Path))
Appreciate your help here!
Is that first table is your data table? Can you explain further how it can show?
please check attached
Hi Shivesh,
Thanks for replying. Unfotunately I am using qlikview desktop version. I can not open attached qvw due to desktop version restriction. Is it possible to send out an expression which was used in the qvw shared else can you explain how this was done?
please refer below
T:LOAD * INLINE [
ID, Path, Total1
202, 6, 21
202, 12, 24
410, 6, 16
410, 12, 22
410, 18, 24
418, 6, 15
418, 12, 21
418, 18, 24
511, 6, 15
511, 12, 21
511, 18, 24
8443, 6, 12
8443, 12, 18
8443, 18, 24
92663, 6, 11
92663, 12, 17
92663, 18, 23
92663, 24,24
];
Create a new input box with variable - - - vSelect
Expression = if(len(vSelect)=0 ,sum({<ID,Total1 = {"$(=max(Total1))"}>}Total1),sum({<ID,Total1 = {"$(vSelect)"}>}Total1))
Dimension = ID, Path
If you put value in your input box, then that value will be assigned to variable and corresponding to variable you'll get the values. I have put variable value condition for Total, if it's different, let me know.
Thanks Shivesh, This helps but with this I am getting below output if i pass variable value as 15.
ID | Path | Total |
418 | 6 | 15 |
511 | 6 | 15 |
instead I want output to the nearest input value. Example below for the value passed as 15.
ID | Path | Total |
202 | 6 | 21 |
410 | 6 | 16 |
418 | 6 | 15 |
511 | 6 | 15 |
8443 | 12 | 18 |
92663 | 12 | 17 |
HI Shivesh,
Managed to get desired output,just extended your expression bit further and removed Path from the dimension
if(len(vSelect)=0 ,sum({<ID,Total1 = {"$(=max(Total1))"}>}Total1),FirstSortedValue ( {< ID,Total1 = {">=$(vSelect)"} >}Total1,Path))
Appreciate your help here!
Glad to know it helped, please close the thread by marking answer as correct.