If no output from Join, count as 0

[emiafshar]

Hi there!

I join a today file and yesterday file to get the following:

1. L output as my closed (if the item is not in today file)

2. J output as my current open

3. R output as my new trades (if not in yday file)

Some days, there aren't any trades in the L output and because i later append the 3 outputs to give me my close, new and open the append then fails as there is no output in L.

How can I get something to come out of my blank output to tell me if it's a 0?

[danilang]

Hi @emiafshar 

If you use a Count tool instead of the Summarize, it will return a row even if there are no records in the input.  In this case the count will be 0

Dan

[emiafshar]

@danilang Hi Dan, I did try this but today when i did have an output, it gave me a count of records rather than the actual count of clsoed. Need something that would work in both instances as it's a scheduled daily workflow 

[ponraj]

You can use count records tool. Count will be zero if you append it to your blank output. 

[RaviP]

Hi @emiafshar , not sure if this what you want.

May be you could do something like this?

Edit:

So, if there are no records from the left output, it will give a zero.

And if there are records in the left output, it would give the count

Both summarize object should have the same field.

Let me know if this works.

[HomesickSurfer]

Hi @emiafshar

Create a [STATUS] field for each join output...for downstream isolation/separation, and; UNION the three streams, setting a specific order - ensuring that you always have a dataset

[emiafshar]

@HomesickSurfer if there is no output from close, it'll just show counts for status new and current open. Is there logic that can then be applied to identify if close is missing to always populate with 0?

[HomesickSurfer]

Hi @emiafshar 

Do you want records, or counts?

[emiafshar]

@HomesickSurfer if there is data i.e. there are 5 closed records from the previous data file, i would want the output to be Closed = 5. If there is no data, i would want the output to be closed = 0.

Hope that makes sense!

[HomesickSurfer]

Hi @emiafshar 

Aha!...ok, union an empty record in a Text Input tool as placeholder to each join output, and; summarize by grouping by STATUS then CountNonNull items.  Let me know how it works for you.