The Data getting duplicated against order number(Suppresion of duplicate datas)

[#1]

Dear All,

I have following 3 tables related to service history of a vehicle

1.WorkDone

2.PartsHeader

3.PartsUsed

When i join these 3 tables and i create a pivot table am getting a table like below.

DealerCode	OrderNo	JobDescription	JobType	PartsUsed
A	12000002	SECOND FREE SERVICE	Warranty	CONSUMBLES&CLEANING MATERIAL
				CRTG-AIR FILTER
				ENGINOIL M
				OIL FILTER
				STRAINER ASSY-F
				WINDSCREEN WASH
		WHEEL ALIGNMENT	Dealer Paid	CONSUMBLES&CLEANING MATERIAL
				CRTG-AIR FILTER
				ENGINOIL M
				OIL FILTER
				STRAINER ASSY-F
				WINDSCREEN WASH
		WHEEL BALANCING	Dealer Paid	CONSUMBLES&CLEANING MATERIAL
				CRTG-AIR FILTER
				ENGINOIL M
				OIL FILTER
				STRAINER ASSY-F
				WINDSCREEN WASH

But this is not what i need, i need single information against the order no like below

DealerCode	OrderNo	JobDescription	JobType	Parts
A	12000002	SECOND FREE SERVICE	Warranty	CONSUMBLES&CLEANING MATERIAL
				CRTG-AIR FILTER
				ENGINOIL M
				OIL FILTER
				STRAINER ASSY-F
				WINDSCREEN WASH
		WHEEL ALIGNMENT	Dealer Paid	-
		WHEEL BALANCING	Dealer Paid	-

For your reference i have attached source excel and an application i worked.

I would be great ful if you people provide an idea or solution to my query.

Thanks in Advance

[#2]

Dear All,

Am Sorry, I forgot to add the Result Table Structure File. This is the expected output.

Thanks in Advance.

[Gysbert_Wassenaar]

Change the Part_Used dimension to a calculated dimension: =if(WD_JobType='Warranty',[PU_Parts])

[#4]

Hi Gysbert,

Gysbert, Thanks for looked in to my issue.

Actually the problem is, i don't have any parts information related to jobdone like what u said ( If JobType=Warranty then parts).

There is no any such information in the parts field.

simply in the parts field i have parts used with doc number which is related to parts header. In parts header i have order number and dealer code which is common to jobdone table. there is no any direct relation between parts used table with job done table, that is the problem and that is why am getting the table as output like i mentioned in my post.  now i need to construct the table like what i mentioned in result table excel.

Thanks to you once again.

[#5]

Dear All,

Any ideas from anyone for my issue.

Thanks in Advance.

[#6]

Hi Techies,

The parts information is not only related to warranty, sometimes it may come for dealer paid or customer paid so i can't  give a condition like jobtype is equal to warranty then parts.

Is there any possibility to relate those fields by autogenerate or by using any functions or techniques.

Techies share any ideas or suggestions for my issue.

Thanks in Advance.

[#7]

Hi Arun,

Is there a way to Identify the parts whether  it is Dealer paid, Customer Paid or warranty?

Its tough to link with out identifying...

Can you send the raw data and result table in an excel where in next time suppose if "Dealer paid" for the "parts" instead of Warranty then what is the output you are expecting?

Thanks,

Bhaskar

[vincent_ardiet]

Hi,

With your data it seems to be impossible to obtain what you want. You have nothing to link data coming from table3 only with warranty. You have a problem with your join key. Or, you are sure to know your values and you can apply the Gysbert's solution.

Regards,

Vincent