How to identify primary key in a table seperated by comma.

[#1]

Dear Friends

I have two input table

Table 1:

Here Supplier ID is numeric field where mutiple supplier linked with the same contract are enter using comma.

Contract ID	Supplier ID	Project Status
123	2	Green
145	1,4,6,7	Yellow
167	3,8,9	Red

Table 2:

Here for every supplier ID the respective article & spend is mentioned.

Supplier ID	Article	Spend
1	1234	100
1	1344	100
1	1454	100
2	1564	100
2	1674	100
3	1784	100
3	1894	100
4	2004	100
5	2114	100
6	2224	100
6	2334	100
7	2444	100
7	2554	100
8	2664	100
9	2774	100
9	2884	100

I want to

• count no of article as per contract id ( so it should count the artcle number for all supplier seperated by comma)
• Sum Spend as per contract id

I am not able to connect the two table as the data in table 1 is seprated by comma & hence not acting as a primary key.

Please help me in case if there is a way around to deal with such issues.

Many Thanks

Best Regards

Rahul

[sujeetsingh]

Hi,

Try

subfield(SupllierID, ',' ,1)  it will give you 1 from value 1,2,3

subfield(SupllierID, ',' ,2)  it will give you 2 from value 1,2,3

so use this logic and try it or try giving a sample to us.

[#3]

Hi

Thanks for your reply.

Enclosed is the example.

But the supplier id can be different. here i have shown just an example.

is it possible it takes data from table 1 for such logic.

[#4]

TABLE1:

    CustomerID, SupplierID

    from table1

RESULT:

lOAD

CustomerID,

SubField(SupplierID,',',ITERNO()) AS SupplierID

RESIDENT TABLE1

WHILE(ITERNO()<=SubStringCount(SupplierID,',')+1);

TABLE2:

LOAD

SupplierID,Article,Spend

from table2;

[#5]

firstly make table1 as given below.

table1:

LOAD * INLINE [

    Customerid, supplierid, projectstatus

    123, 2, green

    145, "1,4,6,7", yello

    167, "3,8,9", red

];

RESULT:

lOAD

Customerid as customerid_new,

projectstatus as projectstatus_new,

SubField(supplierid,',',ITERNO()) AS supplierid_new

RESIDENT table1

WHILE(ITERNO()<=SubStringCount(supplierid,',')+1);

[#6]

this is the correct code according to your requirement.

table1:

LOAD * INLINE [

    Customerid, supplierid, projectstatus

    123, 2, green

    145, "1,4,6,7", yello

    167, "3,8,9", red

];

RESULT:

lOAD

Customerid as customerid_new,

projectstatus as projectstatus_new,

SubField(supplierid,',',ITERNO()) AS supplierid_new

RESIDENT table1

WHILE(ITERNO()<=SubStringCount(supplierid,',')+1);

table2:

LOAD supplierid as supplierid_new,* INLINE [

    supplierid, article, spend

    1, 1234, 100,

    1, 1344, 100,

    1, 1454, 100,

    2, 1564, 100,

    2, 1674, 100,

    3, 1784, 100,

    3, 1894, 100,

    4, 2004, 100,

    5, 2114, 100,

    6, 2224, 100,

    6, 2334, 100,

    7, 2444, 100,

    7, 2554, 100,

    8, 2664, 100,

    9, 2774, 100,

    9, 2884, 100

];

drop Table table1;

then output like this

supplierid_new	customerid_new	projectstatus_new	article	spend
1	145	yello	1234	100
1	145	yello	1344	100
1	145	yello	1454	100
2	123	green	1564	100
2	123	green	1674	100
3	167	red	1784	100
3	167	red	1894	100
4	145	yello	2004	100
5			2114	100
6	145	yello	2224	100
6	145	yello	2334	100
7	145	yello	2444	100
7	145	yello	2554	100
8	167	red	2664	100
9	167	red	2774	100
9	167	red	2884	100

[sujeetsingh]

You can refer this ....

[#8]

Dear Vishwaran

Thanks for your reply.

But my table cannot be an inline table. This is just a sample data.

Table 1 will be taken directly from the server.

the supplier id in table 1 can change during each reload.

Request your views.

[#9]

Hi SOS,

Thanks for your reply.

But my table cannot be an inline table. This is just a sample data.

Table 1 will be taken directly from the server.

the supplier id in table 1 can change during each reload.

Request your views.

[#10 · Accepted solution]

ok, there is no any issue of inline, i had provide only the sample, you load table 1 according to you.like this

table1:

LOAD

    Customerid,

supplierid,

projectstatus

from table1;

RESULT:

lOAD

Customerid as customerid_new,

projectstatus as projectstatus_new,

SubField(supplierid,',',ITERNO()) AS supplierid_new

RESIDENT table1

WHILE(ITERNO()<=SubStringCount(supplierid,',')+1);

table2:

LOAD

supplierid as supplierid_new

article,

spend

from table2;