Recursive Sort/Search?

[oradba4u]

All:
I have a client who owns/rents several hangars at an airport.
He wants to see which airplane frequents each of his 10 hangars the most over a 12-month period.

I have a Hangar log for that 12 month period. It consists of:
Hangar No.
Tail ID
Date

I have written the code to place each hangar in the proper order (1,2,3,4,5,etc.) each day, with the TailID but:
How do I count the occurrences of each TailID in a particular hangar, and order that list so that the top tailID for each hangar floats to the top? i.e. the top plane to visit hangar#1, Hangar#2, etc.
Some sort of recursive search/sort routine, where the plane with the most visits to a particular hangar in a year (by tailID) will be the only ones listed (for a total of 10)

Sample data:
1 - N3948576
2 - N4309385
3 - NS5930432
4 - .
5 - .
6 - .    etc., etc., etc.
7 - .
8 - .
9 - .
10- .
NEXT DAY
1 - N4309385
2 - N3948576
3 - NS5930432
4 - .
5 - .
6 - .    etc., etc., etc.
7 - .
8 - .
9 - .
10- .
NEXT DAY
and so on, and so forth...
each day could be totally different from the previous day

The FINAL report should only be 10 entries:
1 - N374764 (this plane had the most times in Hangar#1 for the entire year)
2 - NS6675753358 (this plane had the most times in Hangar#2 for the entire year)
3-10 same output as 1 & 2 but with different data

I don't know if I have explained this correctly, but I hope someone catches on to what I'm trying to accomplish.

As always, thanks in advance

Stymied in Stubenville

[stanl]

If your log could be formatted as csv -  Hangar No.,Tail ID,Date - import to Excel, a simple Access table, a good candidate for Jim's SQLite extender, use LogParser [I think it is still available and free and multiple scripts on Tech Database]... any of these would work - just post some fake data as .csv

[oradba4u]

Great ideas, but I am hoping/wanting a winbatch solution as this is one of several programs that I want to tie together, and the user might not have other apps (Excel, Access, etc.)

[ChuckC]

The SQLite extender would then be your best bet.  It doesn't depend on any other software being installed.

Alternatively, consider using the new "map" construct in WinBatch.

Something along the lines of the following:

Create a primary map where the hanger number is the key, and the data item for each hanger number in the map is itself another map where the tail id is the key and the data item is an integer representing the count of how many times that plane has been in the specified hanger.

Read thru your in put data one row/record at a time, and provided that the date of the record falls within the start & end dates that you specify, either create new map entries or update existing map entries count values as you process each row/record.

When you care done iterating thru the input data and populating the maps, then iterate thru the primary map of hanger numbers, and thru each subordinate map looking for the highest count value, which you can then stash on the side in an additional map of hanger numbers or in some other construct.

The point is, this is all going to comfortably fit in memory and you don't have to do any complex sorting.  All you have to do is just perform insert/update operations on maps and then iterate thru the maps to get the results you want.  I would recommend doing some data scrubbing in the event that are inconsistencies in the data input process, such as mixed case, leading/trailing white space, etc..., so you don't get split counts where a hanger or plane appears 2 or more times with slightly different values.

[oradba4u]

I'll give it try, and thanks a lot. I'll let you know how it ended up!

P.S. Where might I get this SQL lite extender? The Tech Database is very slow and sketchy right now, and I am eager to press forward.

[KeithW]

just search the forum here for SQLITE there are a couple threads and links where to download the extender.
I believe the last release was "R"  as in  wbsql44i_vR  around the 6th of June 2020.

Keith

[stanl]

Alternatively, consider using the new "map" construct in WinBatch.

Or, what I neglected to mention in my first response - a Fabricated Recordset. The benefit would be storing the dates as a type, as would the Extender.

[JTaylor]

Here is a link...

    http://www.jtdata.com/anonymous/wbsql44i_vr.zip

Have meant to follow up on the Extender a bit more and make it somewhat "official" but my house flooded and am dealing with that.   Hopefully soon though.

Jim

[KeithW]

Jim,

Sorry to hear about the house, hopefully it works out for you.  Have a few friends up in north-central IL that dealt with River flooding, their experience has been terrible unfortunately, happened twice and they are probably going to have to bulldoze and start over.

Keith

[JTaylor]

Thanks.   Could have been much worse but a lot of money, work and time involved making it right, even so.

Jim

[oradba4u]

@ChuckC:

I'm afraid the "Box" thing is beyond my comprehension... That being said, is there a paper or something that details this feature? From what I've seen, I can't get my small ADHD brain around what I've read thus far. ;D

Also, this SQLlite extender... there is SQLLite somehow built-into Winbatch? as you stated "It doesn't depend on any other software being installed". As you can see, I'm confused now, and I don't see any examples on how to use it. Guess I need to look deeper? Besides, I'm pretty sure my customer does not want more software installed (SQLLite?) on his server. I'd like to deliver a full Winbatch solution with a compiled .exe file!

What I'm really looking for is a total Winbatch (WIL) solution... I'm not asking for code, just wondering if someone before me has solved this problem (and how), and could send an algorithm or code "snippet".

Anyway, thanks for helping me with this problem!

[JTaylor]

Wasn't there a Help file in the zip?    You may need to "Unblock" it under File Properties.   There are examples in it.    It is no different than any other Extender.   You don't have to install SQLite.    It is all WinBatch.

Jim

[JTaylor]

I assume you mean "Map" instead of "Box"?    If so, assuming you have a recent version of WinBatch look for "Map Functions" in the Help file.   There are examples there.

Jim

[oradba4u]

Yes, I was talking about MAP and not BOX!

I will look at the help file and see what I can gleen.

MANY THANKS!!!!

[ChuckC]

A WIL "Map" is effectively the same thing as a "Dictionary", if you happen to have used dictionary objects in vbscript, or perhaps in c# or C++.

[stanl]

Regardless of what route you choose, the original caveat Chuck first mentioned is significant:


I would recommend doing some data scrubbing in the event that are inconsistencies in the data input process, such as mixed case, leading/trailing white space, etc...

In addition:

   
• Maps are 'pairs', so working through your data with Maps will be determined by the expected output for the client
• Your ask assumes a single output YTD by Hanger by Plane. What if the client then says "can you break it down monthly"

You need to post some of the log in its original format, otherwise this thread may go round and round for a trivial task.

[ChuckC]

Computing the results broken down on a monthly basis wouldn't be any different than computing it for a year with the methodology that I was recommending with the use of maps/dictionaries.

Repeat the processing once for each month and only include records that have dates falling within the specified month.

Alternatively, introduced another map at the highest level that has an entry in it for each month and then each of those entries refers to a map containing all the hangers for which there was traffic in its corresponding month.

[oradba4u]

No experience with Dictionary's in any language, so this concept is totally new to me...

I read what chuck is saying, but "no comprendo." It just goes over my ADHD tuned brain.

Is there a way to solve this using arrays, and recursion in checking to see if the array element has been counted before?

Here's what I'm trying to do: (see attached pic)

Eventually, I'll want to cycle through ALL 10 hangars for the entire year, and list the top tail_ID for each hangar (not just hangar #5 as the example shows)

[ChuckC]

A map/dictionary is also referred to as an "associative array".  Instead of being of a fixed length with an integer value used as the index, it is dynamic in size and uses a string as the lookup key instead of an integer index value.

I would suggest to you that it is time for you to do some reading and gain a basic understanding of the terminology involved here.  Without doing so, your best bet is to just pay somebody to develop it for you on a contract basis.

I see that you did introduce a new constraint on the data that wasn't clearly stated in your original post.  According to the spreadsheet screen snapshot you posted, it appears that any given hanger can only accommodate a single airplane.  Additionally, you noted that there could be a tie involving 2 or more aircraft for any given hangar for the time period being reported on, and that also has an impact on how you write code to do the data manipulation and reporting.

It may well be possible to get the results you want simply by using Excel to generate a stacked bar chart or histogram based on the data.  Again, that's more reading that needs to be done on  your part.

[oradba4u]

I cannot depend on any user to have Excel or any other 3rd party programs (MySQL, Excel, etc.) installed, so I'm looking for an all-Winbatch solution, as a compiled .exe file is self-dependent.

Anyway, thanks for all you guys help... I KNOW this problem can be somehow solved with straight WinBatch code (loops, arrays, etc.), but I can't yet grasp it, and my brain ain't what it used to be (i.e. I can't get my head around MAPS, for example)

I think what I need is to develop an algorithm and flow chart to break this down. In my mind, some sort of recursion is required to count the different tail_ID's (as well as how many times a particular tail_ID was in hangar#5 for the entire year), for example.

I'll keep pluggin' away though. "Somewhere out there"...

[stanl]

Stone me if I am wrong.... but


This seems more like a homework assignment rather than a real business problem. I have flown in two private planes as an employee and each was assigned to a hanger via a contract... i.e. the plane sits in the hanger until used, then returns to the hanger.


The Op's screenshot looked like the data was already in an application like Excel. Think I earlier suggested this could go 'round and round' and if these remarks seem offensive to the OP... I apologize.

[ChuckC]

LOL... That same thought crossed my mind.

The solution to the problem is quite trivial, though, and the problem should yield easily under the pressure of sustained logical thinking.

What I find confounding, though, is the "willful ignorance" approach to the suggested methods of solving the problem.  If as much effort were put into solving the problem as in explaining all the reasons why it's so hard to solve, it would have been solved already.

I did have something run through my mind... "I could do it for you, but then I'd have to bill you...".

[stanl]

LOL... That same thought crossed my mind.

The solution to the problem is quite trivial, though, and the problem should yield easily under the pressure of sustained logical thinking.

I did have something run through my mind... "I could do it for you, but then I'd have to bill you...".

So, what if it was Gates, in a horse race, not hangers. The ask is to find the gate that has the highest probability of producing a winner in the top 3 [regardless of the horse]. Not going to guarantee any money but an interesting probability to calculate. I would volunteer that for free. ;D