QLIKSENSE FILTER AND QUERY ON THE FLY

[Anonymous]

Hi Everyone,

Can you help me on this one please? I wish to filter the ORDER based on the values from two fields; INTERVAL(PREV-REC) and      INTERVAL(REC-NEXT).

RECORDS:

ORDER        ORDER DATE          INTERVAL(PREV-REC)        INTERVAL(REC-NEXT)

A                  16/5/2016                   -                                            28

B                  13/6/2016                  28                                          32

C                   15/7/2016                  32                                        34

D                  18/8/2016                  34                                         35

E                  22/9/2016                  35                                         22

F                  14/10/2016                22                                           27

G                  10/11/2016                27                                         41

H                  21/12/2016                41                                         36

I                  26/1/2017                    36                                         47

J                  14/3/2017                  47                                            -

Say I want to filter and only display ORDER where the interval = 22. This would mean that only ODER E and ORDER F will be shown.

and if I filter for interval = 27, ORDER F and ORDER G will be shown. and so on...

The filter is dynamic, hence what i did is i defined an inline table. and then

Table:

Load*Inline [

DAYS

1

2

3

4

5

6

7

8

9

10

...

.....

50

];

Thought i could use the function GetFieldSelections() but I couldn't make it work. please help advise if this is possible. Many thanks in advance!

[felipedl]

Hi Eric,

How to you get the values for Interval(PREV-REC) and Interval(REC-NEXT), PREV, REC and NEXT, what's the logic behind it?

You could do the calculations on script level and than create a field out of the above mentioned fields instead of creating it in the UI part.

[martinpohl · Accepted solution]

create an additional table:

load

order,

"interval(pre-rec)" as interval

resident RECORDS;

load

order

"inerterval(rec-next) as interval

resident RECORDS;

then you can selectyour values in field interval

regards

[Anonymous]

Hi Felip,

Thanks for the reply. The values for PREV-REC and REC-NEXT are the days difference from the other purchase orders, I have this pre-calculated during the loading script. Basically, my tasks was to show possible duplicate orders whereby order records have similar amount, bought from same supplier for the same department etc. and lastly to show the days in between when these orders were posted. I'm good now, and loaded these intervals in the scripts. Sorry, i got hang up on using the GetFieldSelections().

Thanks,

Eric

[Anonymous]

Hi Martin,

Thanks for the reply.

I'm good now, i've loaded these intervals in another table.

Sorry, i got hang up on using the GetFieldSelections() and didn't thought of the simple solution.

Thanks again!

Eric