Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Compare 2 list of dates withtout batch macro

Bargeton
7 - Meteor

Hi,

 

I try to compare 2 list of dates, but not really found a "smart" way to do it...

 

[Table 1] I have a list of object with their created date (yyyy-mm)

Object IDCreate Date
O12022-06
O22022-06

O3

2022-07

 

 

[Table 2] And I have a list of used objects per month:

Date UsedObject Id
2022-06O1
2022-07O1
2022-08O1
2022-08O3
2022-09O1
2022-09O2
2022-09O3

 

 

[Table 3] And I have a list of date to compute the result for each month:

Date
2022-05
2022-06
2022-07
2022-08
2022-09

 

 

What I try to compute is the number of object non used per month.

 

In my example:

  • 2022-05 : all object didn't exist yet --> not used = 0
  • 2022-06 : O1 is used but not O2 ; O3 didn't exist yet (so not to take into the SUM for this month) --> not used = 1
  • 2022-07: all objects exists, O1 is used, but not O2 and not O3 --> not used = 2
  • 2022-08 : all objects exists, O1 and O3 are used but not O2 --> not used = 1
  • 2022-09: all objects exists and used --> not used = 0

 

 

My first idea is to do a batch macro on each date of [Table 3] , example for 1 step : process date "2022-06".

This batch macro take the 2 other full tables [Table 1] and [Table 2] in input and process like this:

  • [Table 1] Keep all objects with created date <= date in input of the macro.
    • For input "2022-06" --> keep O1 and O2 (and we can count 2 existing objects)
  • [Table 2] Keep all lines with date used == date in input of the macro
    • For input "2022-06", keep only first line with [2022-06 | O1] --> count used objects = 1
  • Finally number of not used object is "count existing object" - "count used objects" = 2 - 1 = 1

 

But is there a smarter way to do this without a batch macro?

 

Alex

2 REPLIES 2
Ladarthure
14 - Magnetar
14 - Magnetar

Hi @Bargeton

 

please find attached a workflow doing what you ask.

 

The trick I used is around using a append field tool and a formula which allowed me to have all periods and all objects and then comparing dates to be able to tell if it was expected or not. Then I did an aggregate version and a detailed version depending on your needs

Bargeton
7 - Meteor

Hi,

Thanks for the answer. It's working fine.

I always forget to use the "Append Fields Tool".

Labels