Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
How to left join values from one table to another only first row in Data Load?
Example:
Table A:
Date Product Owner Product Color
20220101 John Product1 Green
20220101 John Product1 Yellow
20220101 Jessica Product1 Black
20220101 Jessica Product1 Blue
20220101 Steven Product1 Grey
20220101 Steven Product1 Red
Tabla B:
Date Product Owner Product Sales Target
20220101 John Product1 2000
20220101 Jessica Product1 3000
20220101 Steven Product1 6000
Result Table:
Date Product Owner Product Color Sales Target
20220101 John Product1 Green 2000
20220101 John Product1 Yellow
20220101 Jessica Product1 Black 3000
20220101 Jessica Product1 Blue
20220101 Steven Product1 Grey 6000
20220101 Steven Product1 Red
Need ignore other fields which aren't at table B and match only first row.
Thanks.
@DoubleG Maye be like :
TableA:
load *,rowno() as Flag inline [
Date,Product Owner,Product,Color
20220101,John,Product1,Green
20220101,John,Product1,Yellow
20220101,Jessica,Product1,Black
20220101,Jessica,Product1,Blue
20220101,Steven,Product1,Grey
20220101,Steven,Product1,Red
];
left join
load * inline [
Date,Product Owner,Product,Sales Target
20220101,John,Product1,2000
20220101,Jessica,Product1,3000
20220101,Steven,Product1,6000
];
output:
load Date,[Product Owner],Product,if(Date&[Product Owner]&Product=peek(Date)&peek([Product Owner])&peek(Product),'',[Sales Target]) as [Sales Target] resident TableA order by Flag;
drop table TableA;
output: