Logic problem in table

[#1]

Dear All,

I have this kind of data:-

Model	Type	Material Code	Amt
S55	Raw	1	12
S55	Raw	2	15
S55	Raw	3	47
S55	SFG	6	25
S55	SFG	23	20
S55	SFG	45	16
S55	FG	250	123
S56	Raw	1	12
S56	Raw	14	25
S56	Raw	16	78
S56	SFG	23	20
S56	SFG	50	96
S56	SFG	63	47
S56	FG	78	15
S57	Raw	1	12
S57	Raw	2	15
S57	Raw	99	96
S57	SFG	45	16
S57	SFG	30	25
S57	SFG	9	12
S57	FG	250	123

now my question is:-

my material ( 1)- occurs in all three model. So when i want to display the data in chart, i want amount for the material against model where it occurs first., and if the same material occur in other model then the amount should be zero.

For example;

Material code starting with 1 occurs in all three model i.e S55,S56,S57 and its amount is 12.So i want amount against S55 model because the material first occurred there and for the remaining model it should show zero value.

Please Help me out.....

[jfkinspari]

You can probably utilize the exists function like this

Load Model, Type, [Material Code],Amt, if(exists(Amt), 0, Amt) as ModifiedAmt

From...

and then use the field ModifiedAmt in your table. It should know only hold a a value above 0, for the first record with a unique Material Code.

[#3]

Hi,

You could reload your table in the script to set to 0 those values that are repeated in the same model:

NewTable:

Noconcatenate LOAD

     Model,

     Type,

     [Material Code],

     if(Previous([Material Code])=[Material Code], 0, Amt) as Amt

Resident YourTable

Order By [Material Code] asc;

Good Luck.

[#4]

How can i fit the above formula in front side

[VishalWaghole]

Hi,

PFA hope that will solve your problem.

Thanks,

Vishal

[#6]

@ VishalWaghole

I cannot fit ur formula on back end because fields are from different table and they cannot be brought into one