About Concatenation

[Anonymous]

Hello everyone,

Can you suggest some solutions to combine 2 tables to desired one.

Thanks for your attentions.

[Peter_Cammaert]

Can you post your source data as text instead of images? Saves time for a lot of members that are trying to come up with a solution.

[jonas_rezende]

Hi, THACH VO NGOC.

Excuse me. What's the logic in removing AA and BB, and keeping Full process FL?

Regards,

Jonas Melo.

[Anonymous]

Thanks for your reply Peter. Here is the text.

//NoConcatenate

//TableInline01:

//load * Inline

//[PQ,CBT.CC,PY.IDPY,CBT.Per

//NN GB-EPW ,GB-EPW,2277122771,1

//NN GB-EPW ,GB-PW,2277122771,2

//NN GB-EPW ,GB-GCB,2277122771,2

//NN GB-EPW ,GB-Wa DR,2277122771,2.5

//NN GB-EPW ,GB-Wa Ara NC,2277122771,2.5

//];

////

/

//load * inline

//[CBB.IDPY,CBB.CC,CBB.Per

//2277122771,GB-EPW,5

//2277122771,GB-PW,5

//2277122771,GB-GCB,5

//2277122771,GB-Wa Ara NC,5

//2277122771,Full process FL,89

//2277222772,AA,90

//2277222772,BB,10

//];

[Anonymous]

Hi Jonas,

We can use ,PY.IDPY or CB.IDPY

Thank for your reply

[Peter_Cammaert]

The code needed to produce a picture like this is ugly and probably can't be reliably applied to a larger data set.

Can you describe how these tables should be combined?

As Jonas already stated, there is something weird with the removal of AA & BB. Which leads me to conclude that only CBB.IDPY values that already exist in PY.IDPY should be retained, correct? 

[Anonymous]

Hi Peter,

Yes, you are right, only CBB.IDPY values that already exist in PY.IDPY should be retained.

[Peter_Cammaert · Accepted solution]

OK. Try this:

TableInline01:

LOAD * INLINE [

PQ,CBT.CC,PY.IDPY,CBT.Per

NN GB-EPW ,GB-EPW,2277122771,1

NN GB-EPW ,GB-PW,2277122771,2

NN GB-EPW ,GB-GCB,2277122771,2

NN GB-EPW ,GB-Wa DR,2277122771,2.5

NN GB-EPW ,GB-Wa Ara NC,2277122771,2.5

];

MapIDPY2PQ:

MAPPING LOAD DISTINCT [PY.IDPY], PQ RESIDENT TableInline01;

CONCATENATE (TableInline01)

LOAD ApplyMap('MapIDPY2PQ', [CBB.IDPY]) AS PQ,

     [CBB.IDPY] AS [PY.IDPY], [CBB.CC] AS [CBT.CC], CBB.Per INLINE [

CBB.IDPY,CBB.CC,CBB.Per

2277122771,GB-EPW,5

2277122771,GB-PW,5

2277122771,GB-GCB,5

2277122771,GB-Wa Ara NC,5

2277122771,Full process FL,89

2277222772,AA,90

2277222772,BB,10

] WHERE Exists([PY.IDPY], [CBB.IDPY]);

TableFinal:

NOCONCATENATE

LOAD PQ, [CBT.CC], [PY.IDPY],

     Sum([CBB.Per]) AS [CBB.Per], // Sum always returns a value

     Sum([CBT.Per]) AS [CBT.Per]

RESIDENT TableInline01

GROUP BY PQ, [CBT.CC], [PY.IDPY];

DROP Table TableInline01;

It works for those few rows in your screenshot, but it's still ugly code.

And I'm almost certain that you'll be back with information about why it doesn't work in your situation (e.g. full data set)

[Anonymous]

Dear Peter, that's great ! I could apply your guide for my specific case.

Thank again for your honest and prompt support.