topic Display record in straight table based on the Input value in QlikView

Display record in straight table based on the Input value

ashishbhuyekar — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Display record in straight table based on the Input value

Anil_Babu_Samineni — Sat, 02 Jun 2018 04:22:42 GMT

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

Re: Display record in straight table based on the Input value

shiveshsingh — Sat, 02 Jun 2018 09:43:42 GMT

please check attached

Re: Display record in straight table based on the Input value

ashishbhuyekar — Sun, 03 Jun 2018 15:19:04 GMT

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?

Re: Display record in straight table based on the Input value

shiveshsingh — Sun, 03 Jun 2018 16:33:14 GMT

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.

Re: Display record in straight table based on the Input value

ashishbhuyekar — Sun, 03 Jun 2018 20:14:45 GMT

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

Re: Display record in straight table based on the Input value

ashishbhuyekar — Sun, 03 Jun 2018 22:34:58 GMT

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!

Re: Display record in straight table based on the Input value

shiveshsingh — Mon, 04 Jun 2018 09:32:39 GMT

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