topic How to show rows from last and on ? in QlikView

How to show rows from last and on ?

yanivvl0 — Thu, 14 Sep 2017 09:22:12 GMT

Hi to all ,

i have table of programs, for each program can be many status , i need to show all the status from IS_LAST row and on (accending) ,

for example : to show status 20-70 BUT not status 10 :

jow can i do that in straight table ( or pivot ) ? THANKS.

Re: How to show rows from last and on ?

Anil_Babu_Samineni — Thu, 14 Sep 2017 09:25:00 GMT

This?

Sum({<status = {">= 20 <=70"}>} program)

Sum({<status -= {'10'}>} program)

Re: How to show rows from last and on ?

yanivvl0 — Thu, 14 Sep 2017 10:29:29 GMT

for this case its ok , but i need for all cases because IS_LAST can be at any status :

Re: How to show rows from last and on ?

Anil_Babu_Samineni — Thu, 14 Sep 2017 10:30:55 GMT

Means?

Re: How to show rows from last and on ?

Peter_Cammaert — Thu, 14 Sep 2017 10:38:36 GMT

If these aren't dynamically calculated in the UI, you can easily set a flag in your Load script. Imagine your table is called "Data". Then the following could act as a guideline:

LEFT JOIN(Data)

LOAD program,

status as LastStatus

RESIDENT Data

WHERE IS_LAST = 1;

NewData:

LOAD *,

IF (status >= LastStatus, 1, 0) AS LastAndLater

RESIDENT Data;

DROP Table Data;

RENAME Table NewData TO Data;

Re: How to show rows from last and on ?

effinty2112 — Thu, 14 Sep 2017 11:17:13 GMT

Hi Yaniv,

With your data you can obtain this straight table:

program	status	IS_LAST
10025	20	1
10025	30	0
10025	40	0
10025	60	0
10025	70	0
10079	70	1
10085	20	1
10085	30	0
10085	40	0
10085	60	0
10085	70	0

status is the calculated dimension:

=aggr(if(status >=Min(TOTAL <program>Aggr(if(IS_LAST=1,status),program,status)),status),program,status)

Regards

Andrew

Re: How to show rows from last and on ?

yanivvl0 — Thu, 14 Sep 2017 11:52:08 GMT

hi Andrew , great help - THANKS !!

Re: How to show rows from last and on ?

yanivvl0 — Thu, 14 Sep 2017 11:53:14 GMT

HI Peter , i need expression , see below, thanks.