Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
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.

brunolelli87_0-1620488545092.png

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

 

brunolelli87_2-1620488691488.png

 

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

 

Where:
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.

Thanks

 

2 Replies
Gysbert_Wassenaar

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
brunolelli87
Creator II
Creator II
Author

Hello,

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:

 

Voos:
LOAD 
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													 	
FROM
[C:\Users\bruno\Desktop\Voos\DB\Voos\Voos*.txt]
(txt, codepage is 28591, no labels, delimiter is ';', msq);

 

 

This is what I did:

 

Left Join(Voos)
LOAD
	KEYI, 
	Date(Min(DASI)) as min_DASI
Resident Voos
GROUP BY KEYI;

 

 

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

Any other idea?