Display record in straight table based on the Input value

[ashishbhuyekar]

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?

[Anil_Babu_Samineni]

Is that first table is your data table? Can you explain further how it can show?

[shiveshsingh]

please check attached

[ashishbhuyekar]

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?

[shiveshsingh]

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.

[ashishbhuyekar]

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

[ashishbhuyekar · Accepted solution]

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!

[shiveshsingh]

Glad to know it helped, please close the thread by marking answer as correct.