Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
yanivvl0
Creator III
Creator III

How to show rows from last and on ?

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 :

from_last_stts.PNG

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

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Yaniv,

With your data you can obtain this straight table:

program status IS_LAST
10025201
10025300
10025400
10025600
10025700
10079701
10085201
10085300
10085400
10085600
10085700

status is the calculated dimension:

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

Regards

Andrew

View solution in original post

7 Replies
Anil_Babu_Samineni

This?

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

OR

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
yanivvl0
Creator III
Creator III
Author

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

from_last_stts2.PNG

Anil_Babu_Samineni

Means?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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;

effinty2112
Master
Master

Hi Yaniv,

With your data you can obtain this straight table:

program status IS_LAST
10025201
10025300
10025400
10025600
10025700
10079701
10085201
10085300
10085400
10085600
10085700

status is the calculated dimension:

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

Regards

Andrew

yanivvl0
Creator III
Creator III
Author

hi Andrew , great help - THANKS !!

yanivvl0
Creator III
Creator III
Author

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