Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Do not sum if the KEY was previously considered

Hello guys,

I'm using a bar chart with dates (DASI field) on X axis and the sum of seats offered (TOTS field) per airline on Y axis.


So, my dimensions are: DASI and NCIA, as you can see below:




And my expression is: sum({<DASI=>} aggr(sum({<DASI=>} TOTS), KEYI)),


DASI is a date field,
TOTS is the total number of seats
KEYI is the key field.

Basically, each fligh has its own Key.


But, my problem is, once I'm summing the total of seats per KEYI and if the airplane took-off at night and landed on the other day, the system is considering two times this KEYI. Because it's a distinct KEYI on day 1 and a distinct KEYI on day 2.

How can I just consider the KEY (KEYI field) if it has not been previously considered?
So, if the airplane took-off in one day and landed on another day, I would like to consider the seats offered just on the first date.



2 Replies

I think the only way to sort this out is to create a field like TakeOffDate in the load script (if you don't have it already) and use that field as dimension instead of the date field you're now using.

talk is cheap, supply exceeds demand
Creator II
Creator II


Thanks for your kindly support.


I don't have a TAKEOFF DATE field because basically my database contains all aircraft position per minute. So, while the aircraft is flying she is reporting her position... Therefore, I tried to create a left join in order to obtain the first date that the aircraft reported her position.


This is my script:


Date#(Date(@2, 'DD/MM/YY'),'DD/MM/YY') & @6 as KEYP,						
@1 as KEYI,																				
Date#(Date(@2, 'DD/MM/YY'),'DD/MM/YY') as DASI,
Time#(Time(@3, 'hh:mm'), 'hh:mm') as HHSI,														 	
timestamp(date(Date#(Date(@2, 'DD/MM/YY'),'DD/MM/YY') &' '& Time(@3)),'DD/MM/YYYY hh:mm')  as TIMS,    
If(@6='TAM', 'LAN',if(@6='PAM', 'PTB',@6)) as NCIA													 	
(txt, codepage is 28591, no labels, delimiter is ';', msq);



This is what I did:


Left Join(Voos)
	Date(Min(DASI)) as min_DASI
Resident Voos



But, I don't know why it's still nor working.

Any other idea?