Left Outer Join - Fail to map empty cells

[Valerine Tng]

Basically I want to do a left outer join. I have two sources of data -

1. original data with the mapping (CSV file)

2. ERP data from the cloud

The data looks like this in the CSV file, which looks the same as the data from the cloud (via Microsoft Server SQL)

I am trying to sieve out the period activity from the cloud, but somehow Spotfire did not manage to detect the mapping.

If I match either the acc+sub-acct or acc+cc, it works but I cant match all 3 (acc+sub-acct+cc), they do not give me the period activity.

How do I resolve this

 

 

 

Account

Sub-Acct

CC

Period activity

 

 

1

A

 

 

 

 

2

B

 

 

 

 

3

 

X

 

 

 

 

4

 

 

Y

[Fabian Duerr]

Can you please provide more info here. It's not clear to me:

 

How does your first table look like How about the second table What is the desired result

[Valerine Tng]

Table 1

 

 

 

Account

Sub-Acct

CC

Period activity

 

 

1

A

 

 

 

 

2

B

 

 

 

 

3

 

X

 

 

 

 

4

 

 

Y

 

 

 

 

Table 2

 

 

 

Account

Sub-Acct

CC

Header

 

 

1

A

 

 

 

 

2

B

 

 

 

 

3

 

X

 

 

 

 

4

 

 

Y

 

 

 

 

Table 3

 

 

 

Account

Sub-Acct

CC

Period Activity Header

 

 

 

1

A

 

 

 

 

2

B

 

 

 

 

3

 

X

 

 

 

 

4

[Fabian Duerr]

You can replace all empty values with a dummy string and then try again.

1.) Open the DATA panel. Pick your data table(s) and select the column. Then use the option 'Replace empty values with' > 'specific value'. Enter a uniqu stringe that you don't find elsewhere in your column, like 'dummy string for merge' or'NA' or '0'. Whatever you like.

2.) Do the merge