Concat and Count

[beck_bakytbek]

Hi Folks,

i got a situation:

temp:

OrderID, Product, Turnover

101, Road Bikes, 25

101, Chains, 25

102, Road Bikes, 5

102, Footwear, 15

103, Chains, 7

left join

load OrderID, Concat(Product, ' , ' ) as Product

resident temp

group by OrderID;

my resultstable does look like:

OrderID, Products, Turnover

101, Road Bikes, Chains, 50

102, Road Bikes, Footwear, 20

103, Chains, 7

I want to have such tables

OrderID, Products, Turnover

101, 2, 50

102, 2, 20

103, 1, 7

How to implement the ProductsName into Number?

Does anybody have any idea?

Thanks a lot

Beck

[Quy_Nguyen]

Try this:

A:
Load * INLINE [
OrderID, Product, Turnover
101, Road Bikes, 25
101, Chains, 25
102, Road Bikes, 5
102, Footwear, 15
103, Chains, 7 ];

NoConcatenate
Data:
Load 
 OrderID, 
    Count(Product) As Product,
    Sum(Turnover) As Turnover
Resident A
Group by OrderID;

Drop Table A;

[OmarBenSalem]

Try :

temp:

OrderID, Product, Turnover

101, Road Bikes, 25

101, Chains, 25

102, Road Bikes, 5

102, Footwear, 15

103, Chains, 7

left join

load OrderID, SubStringCountConcat((Product, ' , ' ), ' , ')+1 as Product

resident temp

group by OrderID;

[beck_bakytbek]

Hi Omar,

how are you, thanks a lot for your responce, i implemented it, but it does not work

[OmarBenSalem · Accepted solution]

Hi Beck

It worked for me:

load *, SubStringCount(Products,'- ')+1 as count Inline [

OrderID, Products, Turnover

101, Road Bikes- Chains, 50

102, Road Bikes- Footwear, 20

103, Chains, 7

];

result:

Mybe u should recall ur table and then do the transformation:

sthng like:

noconcatenate

load *,  SubStringCount(Products,', ')+1 as NewProdcuct resident urTable;

drop urTable;

[beck_bakytbek]

Thanks a lot Omar for your time and help,

as always i say; shukraan 'akhi

Have a nice evening

Beck

[beck_bakytbek]

Hi Quy,

thanks a lot for your help and time

[nsetty]

AutoNumber(Product) ?

[Quy_Nguyen]

I dont know what's wrong with my answer Beck