Comparing 2 value lists to count duplicates (NOT using flag in Load Script): Booking status analysis

nezuko_kamado — Wed, 29 Mar 2023 22:14:06 GMT

The app should compare a selected month’s future six months booking change. ( butI post a protoptye to simplerfy the issue)

I don’t want to create flags comparisons in load script, because I have to create so many tables
(n!/(n-q)!*q! ) in load script, and which wouldn’t be a good way for the continuing months' analysis. Eventually I want to use a filter pane to choose a month of data saved and created 15 comparisons are displayed.

But BEFORE that, I created a prototype with sample data sets that are four data lists. Each is saved in Nov, Dec, Jan and Feb. IDs for booking saved in November is ID11, and booking data saved in the end of Dev is ID12 so on and so forth.

But first issue is, when checking the boxes of comparison of two months, the last rows bring wrong IDs

if(ID11 = ID12, aggr(concat(distinct ID12, ',') , StayMonth))

and for continued booking IDs in StayMonth of Feb should only have H, not I, J, K., because November doesn’t have I, J, K

The same error shows over the other boxes except the last comparison box of Jan-Feb

The second issue is that the code

if(ID11 = ID12, count(distinct ID12))

doesn’t count the number of distinct IDs that shared in two months. It should count the comparison of Nov and Dec as 2, because of E and F. Yet it shows 1.

NovInput:
load * inline [ ID, ID11, StayNight, StayMonth
A, A, 3, Nov
B, B,4 , Nov
C, C,2, Dec
D, D, 4, Dec
E, E,2, Jan
F, F,3, Jan
G, G, 4, Jan
H, H, 2, Feb
];

DecInput:
load * inline [ID, ID12, StayNight, StayMonth
C, C, 2, Dec
E, E, 2, Jan
F, F, 3, Jan
H, H, 2, Feb
I, I, 4, Feb
J, J, 5, Feb
K, K, 1, Feb
L, L, 3, Mar];

Janinput:
load * inline [ ID,ID1,StayNight, StayMonth
E, E, 2, Jan
F, F, 3, Jan
H, H, 2, Feb
I, I, 4, Feb
K, K, 1, Feb
L, L, 3, Mar
M, M, 2, Mar
N, N, 3, Mar];

FebInput:
load * inline [ID, ID2,StayNight, StayMonth
H, H, 2, Feb
K, K, 1, Feb
L, L, 3, Mar
N, N, 3, Mar];

topic Comparing 2 value lists to count duplicates (NOT using flag in Load Script): Booking status analysis in App Development

Comparing 2 value lists to count duplicates (NOT using flag in Load Script): Booking status analysis