Pattern specific records

[Anonymous]

Hello,

Today I received a very unusual data request and thought I can get some help from the community again. I tried to imitate a similar logic to come up with the exact work scenario. I have table A, B, & C below. Vendor needs information in a specific format as I have at the very bottom on Result section. First field on Result needs to be hard coded letters. D stands for demographic, R for revenue, and P for Procedure. Any solution or advice is greatly appreciated.

Table A
ID	Name	Date
ID101	James	11/1/2017
ID102	Rock	11/2/2017
ID103	Paul	11/5/2017

Table B
ID	Charge	Payment
ID101	$10	$5
ID102	$50	$25
ID103	$30	$15

Table C
ID	Procedure
ID101	1001
ID102	1002
ID103	1004

Result
D	ID101	James	11/1/2017
R	ID101	$10	$5
P	ID101	1001
D	ID102	Rock	11/2/2017
R	ID102	$50	$25
P	ID102	1002
D	ID103	Paul	11/5/2017
R	ID103	$30	$15
P	ID103	1004

[its_anandrjs · Accepted solution]

What is mean by result field or any logic behind this.

You can try this way by concatenating and give same field name for this, and this is not a big task, right.

But please elaborate what is mean by result field is there any logic for this.

//Table A

LOAD ID,    Name,    Date,'D' as Result;

LOAD * Inline

[

ID,    Name,    Date

ID101,    James,    11/1/2017

ID102,    Rock,    11/2/2017

ID103,    Paul,    11/5/2017

];

//Table B

LOAD ID,Charge as Name,Payment as Date,'R' as Result;

LOAD * Inline

[

ID,    Charge,    Payment

ID101,    $10,    $5

ID102,    $50,    $25

ID103,    $30,    $15

];

//Table C   

LOAD ID,Procedure as Name,'' as Date,'P' as Result;

LOAD * Inline

[

ID,    Procedure

ID101,    1001

ID102,    1002

ID103,  1004

];

Output:-

[Anonymous]

Hi Anand,

I meant to say "Output" instead of the "Result". I did not know we can concatenate this way. This is not a big task so I will try to apply your logic. This works for me.

Thanks much.