Create Blank Column with Null Records

[akvsachin]

Hey folks,

Can anyone please help me clear up some confusion with a workflow that’s stopping me from filtering necessary records?

Workflow:
Input SQL Query (past 7 days) → T-2 Business Days Logic → Join with Ledger and SOD info → Join with Config File →

Issue:
I’m unable to figure out this condition —

• Check if the column SQL-PQL-CDE contains values S and P.

• If both exist, pass them forward.

• If any record is missing SQL-PQL-CDE = S, then create a blank column S (with Null values) and append it to all records.

• Similarly, if any record is missing SQL-PQL-CDE = P, then create a blank column P (with Null values) and append it to all records.

→ Then use a CrossTab tool to allocate SQL & PQL codes →

Concern:
Not sure if the Select tool renaming step would work dynamically in this setup.

→ Then Unique Tool → Output

Any suggestions or alternate logic to handle this dynamically would be really appreciated!

---

[davidskaife]

Hi @akvsachin 

Do you have an example workflow you could provide, even with dummy data, that the Community could look at to suggest/advise further?

[jrlindem]

I agree that having an example workflow with some dummy data would be helpful.

That said, and i'm not sure if i'm interpreting the first part correctly, but if you're simply looking for the existence of an the "S" or "P" indicator in a certain field, using basic formula logic can flag those values (see below and attached).  Where the values don't exist you can just call it NULL().  I've broken this into three steps for illustration but you could certainly combine them if needed or you could expand out the returned values if you need different pieces in different columns.

Happy to continue to help if you can provide more context though!  Hope this helps you get down the right path, -Jay

[akvsachin]

Hey @jrlindem @davidskaife ,

I've attached an image of the confusing part in my workflow.
Input would have a number of columns ~20, There are going to be multiple currency codes HKD, USD, MYR and others. I've filtered inputs firstly on the basis of HKD, then false ones with USD or USN and I'm using the other false anchor too.
This would be probably the second last part of the workflow, So the aim here is to assign SQL-PQL Codes according to table 2, i.e., entity id. I want to populate each record according to their S/P code like in Table 4.
I'm dealing with columns specifically Head Account, Entity-ID, Currency Code, Location Code, SQL-PQL Code, and 10 other columns which would have same values.
The reason to use this filter condition is the SQL-PQL codes assigned with each Head Account / Currency Code / Location Code.
Regarding the HKD Currency Code, mostly for each head account, we get 4 columns from the input query wherein 2 have S Codes whose Location Code is CN and 2 have P codes whose location code is HK. So, I'm using a crosstab tool Where I'm grouping by Head Account and Currency Code, changing headers with SQL-PQL Codes, for values in Entity-ID and choosing method as First.

So, now I join them back with initial so that I receive the records like in Table3(a), 3(b), 3(c). And union them back for the final results.

But, the issue is this union would stop working if either one of the line goes with null inputs while the error would show as "Column is missing 'P'".
Can someone please let me know for a different approach.

 

[dreldrel]

Could you please share your configuration of the union tool? In the Union tool, you should set it to output all the columns and only throw a warning message if there is a column missing from the streams. It seems now you set it to throw an error and stop the process when there isa  missing column

[jrlindem]

@akvsachin are there more values that you're providing in your input?  I ran a simple workflow to go from Table 2 to Table 4 but you've got values that are casting onto CRNCY + Location combo's that don't exist.  For example:

You've got the EntityID's casting onto both the S and P values, even though they don't exist on both S and P.  Is this expected?  You could handle this via formula, but it's something that caught my eye.

Otherwise, you don't have to filter each CRNCY before crosstab'ing.  For example, here's doing them all in one shot:

If you want to get to something that looks like your Table 5, then you can use the crosstab tool in the same way as above but uncheck Location Code and it will roll up to match...

Hopefully this gets you closer, but I do think you need to consider breaking up your goals into different streams instead of forcing the workflow to output each step as you've designed it in your attached file if Table 5 is your ultimate goal.  -Jay