listing according to additional data of some rows

[mhamurcuoglu]

Hi everyone,

I want to clarify my question. Think about there is a database that shows composites analysis results. But, for some level of results, additioanl re-test (detailed analysis) performed. Total database will become generally not detailed analysis data. But inside there will be some additional detailed analysis results. I want to filter in the summary report to see farm data as;

• If there is no detailed analysis of the farm, I want to see the result of "No" answered.
• If for the same number of farm (the same ID), there is a detailed analysis results, instead of "No" answered data, I want to see at the same list "Yes" answered data.
• At the end, summary report will show the lowest level of analysis results in the same place. (instead of farm 02's "No" answer, it will be farm 01, farm 03 and "Yes" answered result of farm 02)

Thanks

farm id	detailed analysis	Analysis results	# of actives
farm 01	No	100	15
farm 02	Yes	50	20
farm 02	No	150	20
farm 03	Yes	90	50

[fvelascog72]

Hi,

You can try with group by farm id.

DATA:

LOAD

  *

INLINE [

farm_id, detailed_analysis, Analysis_results, #_of_actives

farm 01, No, 100, 15

farm 02, Yes, 50, 20

farm 02, No, 150, 20

farm 03, Yes, 90, 50

];

Left Join

LOAD

  farm_id,

  If(Only(detailed_analysis)='No' or Only(detailed_analysis)='Yes', 1, 0) as "FLAG"

Resident DATA

Group by farm_id

;

NoConcatenate

DATA2:

LOAD

  farm_id,

  detailed_analysis,

  Analysis_results,

  #_of_actives,

  If(detailed_analysis = 'Yes', 1,FLAG) as "FLAG"

Resident DATA;

DROP Table DATA;

Saludos.

[mhamurcuoglu]

Thanks Federico

But I want to perform this in the pivot table

[fvelascog72 · Accepted solution]

Try with:

If(Aggr(Only(detailed_analysis), farm_id) = 'Yes' or Aggr(Only(detailed_analysis), farm_id) = 'No', 1,

If(detailed_analysis = 'Yes', 1,0))

Saludos

[mhamurcuoglu]

Federico

Thanks a lot!

[fvelascog72]

You´re welcome