Question about joins.

[#1]

Hi Guys,

Can anyone out there help me solve this issue I have?

Please see below

A:

LOAD * INLINE [

Name, ID

abc123Abandoned Cart_xiyudhu289192, 58973

asdasd234Abandoned Cart_xihu282392, 58975

xsasdass293tBrowers_dhu23432, 56456

];

B:

LOAD * INLINE [

CampaignName, Cost, Date

Abandoned Cart, 3108, 41468

Browers, 827, 41468

];

Result Needed

1 Table That looks like

Name	ID	Cost	Date
abc123Abandoned Cart_xiyudhu289192	58973	3108	41468
asdasd234Abandoned Cart_xihu282392	58973	3108	41468
xsasdass293tBrowers_dhu23432	56456	827	41468

I can't seem to join the two tables where i need to do some sort of dyname string match. 

Please help!

Thanks.

[RedSky001 · Accepted solution]

See the attached file.

[swuehl]

Almost the same approach as Mark, using substringcount and creating a final table:

A:

LOAD * INLINE [

Name, ID

abc123Abandoned Cart_xiyudhu289192, 58973

asdasd234Abandoned Cart_xihu282392, 58975

xsasdass293tBrowers_dhu23432, 56456

];

B:

JOIN (A) LOAD * INLINE [

CampaignName, Cost, Date

Abandoned Cart, 3108, 41468

Browers, 827, 41468

];

RESULT:

LOAD Name,ID,Cost,Date

RESIDENT A

where substringcount(Name,CampaignName);

drop table A;

[#4]

I understand that if the 2 tables I create and wish to join works, but if the 2 tables are huge, this may cause a problem.  Is there another way you guys can suggest or is this the best possible way?  Thanks.

[RedSky001]

Well it's good practice to use numbers for the key fields.  So I've done this in my revised version (attached).

I would just give it ago and see what happes...

[#6]

Appreciate this could you take a look at my follow up question thanks a lot.

[#7]

This is great thanks Mark!