Mapping issue

[buzzy996]

Hello quickers,

can you help how to map these two tables with out using joins.

Table 1:

Column 1: BillNum

-------

R847159 1

R839996/1

R637077 and R699751

R809861 / R816528

R848984 Item no: 8

R758771, R758772, R758773, and R789247

R578433 R545966 R574458 R568711

No.: R848984 Item no: 44

R848914

Column 2:Date

DR-07/05/2017-7949

DR-17/06/2017-7950

DR-19/06/2017-7951

DR-26/01/2017-7952

DR-22/06/2017-7953

DR-24/07/2017-7954

DR-21/06/2017-7923

DR-29/07/2017-7934

DR-19/07/2017-7934

Table 2:

Column 1: BillNum

--------

R847159

R839996

R699751

R816528

R848984

R758772

R574458

R848984

Column 2:EXDate

DR-07/05/2017-7949

DR-17/06/2017-7950

DR-19/06/2017-7951

DR-26/01/2017-7952

DR-22/06/2017-7953

DR-24/07/2017-7954

DR-21/06/2017-7923

DR-29/07/2017-7934

Question: need to map table 2_column 1 with table table 1_column 1 and expected output is table 2 Column 2.

Thanks

[buzzy996]

hi,yes.can u share the solution?..i can check with mine and confirm.

hope u arrived with the above solution without direct joining of the tables (bcz,i do not like to use direct joine in this case)

[antoniotiman]

Table1:
LOAD BillNum,SubField(BillNum1,' ') as BillNum1,Date;
LOAD BillNum,Replace(Replace(BillNum,'/',' '),',',' ') as BillNum1,SubField(Date,'-',2) as Date;   // Date is optional
LOAD * Inline [
BillNum|Date
R847159 1|DR-07/05/2017-7949
R839996/1|DR-17/06/2017-7950
R637077 and R699751|DR-19/06/2017-7951
R809861 / R816528|DR-26/01/2017-7952
R848984 Item no: 8|DR-22/06/2017-7953
R758771, R758772, R758773, and R789247|DR-24/07/2017-7954
R578433 R545966 R574458 R568711|DR-21/06/2017-7923
No.: R848984 Item no: 44|DR-29/07/2017-7934
R848914|DR-19/07/2017-7934
](delimiter is '|');

Left Join
LOAD * Inline [
BillNum1,ExDate
R847159,DR-07/05/2017-7949
R839996,DR-17/06/2017-7950
R699751,DR-19/06/2017-7951
R816528,DR-26/01/2017-7952
R848984,DR-22/06/2017-7953
R758772,DR-24/07/2017-7954
R574458,DR-21/06/2017-7923
R848984,DR-29/07/2017-7934];
Table2:
NoConcatenate
LOAD ExDate,BillNum1 as BillNum
Resident Table1 Where Len(Trim(ExDate)) > 0;
DROP Table Table1;

[buzzy996]

hi thanks for the above,seems u assumed "EXDate" as input column from table  2 but tht's not correct.

i'm expecting that's the output (EXDate field should be generate through "Date column 1 from table 1 by mapping")

any help?

[antoniotiman]

Ok, see new attachment.

[effinty2112]

Hi Shiva,

We can get a straight table like this:

BillNum	EXDate
No.: R848984 Item no: 44	DR-22/06/2017-7953 & DR-29/07/2017-7934
R578433 R545966 R574458 R568711	DR-21/06/2017-7923
R637077 and R699751	DR-19/06/2017-7951
R758771, R758772, R758773, and R789247	DR-24/07/2017-7954
R809861 / R816528	DR-26/01/2017-7952
R839996/1	DR-17/06/2017-7950
R847159 1	DR-07/05/2017-7949
R848984 Item no: 8	DR-22/06/2017-7953 & DR-29/07/2017-7934

EXDate is the expression:

Aggr(if(SubStringCount(BillNum,BillNum1)=1,Concat(EXDate,' & ')),BillNum,BillNum1)

Nothing fancy in the script, just renamed the BillNum field in Table2 to BillNum1:

Table1:

LOAD * INLINE [

    BillNum, Date

    R847159 1, DR-07/05/2017-7949

    R839996/1, DR-17/06/2017-7950

    R637077 and R699751, DR-19/06/2017-7951

    R809861 / R816528, DR-26/01/2017-7952

    R848984 Item no: 8, DR-22/06/2017-7953

    "R758771, R758772, R758773, and R789247", DR-24/07/2017-7954

    R578433 R545966 R574458 R568711, DR-21/06/2017-7923

    No.: R848984 Item no: 44, DR-29/07/2017-7934

    R848914, DR-19/07/2017-7934

];

Table2:

LOAD * INLINE [

    BillNum1, EXDate

    R847159, DR-07/05/2017-7949

    R839996, DR-17/06/2017-7950

    R699751, DR-19/06/2017-7951

    R816528, DR-26/01/2017-7952

    R848984, DR-22/06/2017-7953

    R758772, DR-24/07/2017-7954

    R574458, DR-21/06/2017-7923

    R848984, DR-29/07/2017-7934

];

Cheers

Andrew

PS

With Suppress zero values unchecked:

BillNum	EXDate
No.: R848984 Item no: 44	DR-22/06/2017-7953 & DR-29/07/2017-7934
R578433 R545966 R574458 R568711	DR-21/06/2017-7923
R637077 and R699751	DR-19/06/2017-7951
R758771, R758772, R758773, and R789247	DR-24/07/2017-7954
R809861 / R816528	DR-26/01/2017-7952
R839996/1	DR-17/06/2017-7950
R847159 1	DR-07/05/2017-7949
R848914
R848984 Item no: 8	DR-22/06/2017-7953 & DR-29/07/2017-7934

No match for R848914.

[buzzy996]

thank u,still using EXDate column in script.

i just dropped that column & whr clause and reloaded data ..getting many records.

can u check?

[buzzy996]

thank u, but EXDate is not my source column ,tht's expected out column.

we have to map through BillNum.

any help?

[antoniotiman]

Last Step

Table2:
NoConcatenate
LOAD BillNum1 as BillNum,Date
Resident Table1 Where Len(Trim(ExDate)) > 0;
DROP Table Table1;

Note: I use ExData as Flag to purge Rows not joined.

[buzzy996]

hi,thank u, but my question is ExDate is not data source column,then how we can use for where clause condition?

[antoniotiman]

See Attachment.

ExData not loaded (You can purge in Inline Load)