Empty Tables corrupting joins

James_Jarman

Forums

Hello,
 
I have a dataset where each customer has multiple records for different actions against their record. To be able to perform the requested analysis I need to transform this data such that each customer has a single record in the table (almost a reverse pivot). Generally customers can have up to 20 transactions. I have setup up a boring (and am sure ineffecient) script which identifies the earliest transaction (using summarisation) and then joins back to the base table ignoring this earliest record to get a new population. Then repeat this again and again. Eventually rejoin all the tables together and have a single row per customer with earliest transaction as field 1 progressing to more recent up to field 18.
 
The problem I am having is that occasionally there are only 17 transactions and the 18th table is empty. This is corrupting my final join which then prevents all subsequnet scripts from running. I am hoping you can point me in the direction to write a piece of script which will let me do something like the following:
 
1. If table 18 is populated with at least 1 record proceed with the join
2. If table 18 is empty take a different action eg Rename table 17 to 18 and insert some empty columns allowing rest of my scripts to run correctly.
 
Would appreciate any help you can provide.
Thanks in advance
J

Brian Element Tue, 04/07/2020 - 18:53

Hi J, sounds like you already have your solution.

To check if there are records you can use the db.count function.

Set db = client.opendatabase("My Join File.IMD")

if db.count = 0 then

     rename the file

else

     preform the join

end if

Hopefully this gives you enough to get you going, if not let me know and I will see if I can help.