Data Manipulation

[#1]

I'm having trouble getting my data from two sources to behave the way I want.  I've opted to just present the problem rather than bog you down with my various attempted solutions.  I've attached an example app demonstrating what I'm attempting to do.

I have three tables all stored as QVDs.

• An order table with an account ID.  (From Postgres)
• An account table derived from the order table (Same source, Postgres)
• An account detail table (CRM) from another data source (mySQL) which I merge into the account table.

The problem is sometimes the account detail table (CRM) doesn't have an a matching entry in the account table.  So, I added an account in the CRM to be the account for these missing records.  Let's call this record "ac-no-crm."

All fine and good.  What I have not been able to figure out is how to go through the order table and replace every account ID with the ID of the "ac-no-crm" ID when there is no matching record in the CRM table.  After that, I need to go through the account table and get rid of all the non-matching accounts.

Any suggestions?  I've been trying various combinations of JOINs, resident reloads, and mapping tables and am at the point where I feel that I'm missing some basic concept that should make this simpler than my attempts so far.

Also, tag suggestions would be welcome to make this issue easier for others to find.

Thank you,

Dave

[jerem1234]

Here, let me know if this is what you want. I just joined CRM_Name onto the Orders table, then did a Resident Load to change the name where CRM was null. I used this script:

Orders:

LOAD * INLINE [

  OrderID, AccountID, Amount,

  Order33, Acct1, 11.00,

  Order34, Acct2, 22.00,

  Order35, Acct3, 33.00,

  Order36, Acct4, 44.00,

  Order37, Acct5, 55.00,

];

Left Join(Orders)

Accounts:

LOAD * INLINE [

  AccountID, CRM_Name,

  Acct1, crm1,

  Acct2, crm2,

  Acct4, crm4,

  Acct5, crm5,

  AcctNoCRM, no-crm

];

Final:

NoConcatenate Load

if(isNull(CRM_Name), 'AcctNoCRM', AccountID) as AccountID,

OrderID,

Amount,

if(isNull(CRM_Name), 'no-crm', CRM_Name) as CRM_Name

Resident Orders;

Drop Table Orders;

Please find attached dashboard

[#3]

Close, I think.  I really need the Accounts table and Orders table separate though.  I'll see if I can take your solution and get it working the way I need it to be.

[jerem1234 · Accepted solution]

Will be something like:

Accounts:

LOAD * INLINE [

  AccountID, CRM_Name,

  Acct1, crm1,

  Acct2, crm2,

  Acct4, crm4,

  Acct5, crm5,

  AcctNoCRM, no-crm

];

//The sales table

OrdersTemp:

LOAD * INLINE [

  OrderID, AccountID, Amount,

  Order33, Acct1, 11.00,

  Order34, Acct2, 22.00,

  Order35, Acct3, 33.00,

  Order36, Acct4, 44.00,

  Order37, Acct5, 55.00,

];

Left Join(OrdersTemp)

Load

*

Resident Accounts;

Orders:

NoConcatenate Load

if(isNull(CRM_Name), 'AcctNoCRM', AccountID) as AccountID,

OrderID,

Amount,

if(isNull(CRM_Name), 'no-crm', CRM_Name) as CRM_Name

Resident OrdersTemp;

Drop Field CRM_Name From Orders;

Drop Table OrdersTemp;

Hope this helps!

[#5]

That is perfect.  Thank you so much.  I was making this much more complicated than it needed to be.