title: Building derived column using Spark transformations
body: <p>I got a table record as stated below.</p>
  
  <pre><code>Id   Indicator     Date
  1       R       2018-01-20
  1       R       2018-10-21
  1       P       2019-01-22
  2       R       2018-02-28
  2       P       2018-05-22
  2       P       2019-03-05 
  </code></pre>
  
  <p>I need to pick the <code>Id</code>s that had more than two <code>R</code> indicator in the last one year and derive a new column called <code>Marked_Flag</code> as <code>Y</code> otherwise <code>N</code>. So the expected output should look like below,</p>
  
  <pre><code>Id  Marked_Flag 
  1   Y
  2   N
  </code></pre>
  
  <p>So what I did so far, I took the records in a dataset and then again build another dataset from that. The code looks like below.</p>
  
  <pre><code>Dataset&lt;row&gt; getIndicators = spark.sql("select id, count(indicator) as indi_count from source group by id having indicator = 'R'");
  
  Dataset&lt;row&gt;getFlag = spark.sql("select id, case when indi_count &gt; 1 then 'Y' else 'N' end as Marked_Flag" from getIndicators");
  </code></pre>
  
  <p>But my lead what this to be done using a single dataset and using Spark transformations. I am pretty new to Spark, any guidance or code snippet on this regard would be highly helpful.</p>
  
  <p>Created two Datasets one to get the aggregation and another used the aggregated value to derive the new column.</p>
  
  <pre><code>Dataset&lt;row&gt; getIndicators = spark.sql("select id, count(indicator) as indi_count from source group by id having indicator = 'R'");
  
  Dataset&lt;row&gt;getFlag = spark.sql("select id, case when indi_count &gt; 1 then 'Y' else 'N' end as Marked_Flag" from getIndicators");
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/ofney.png" rel="nofollow noreferrer">Input</a></p>
  
  <p><a href="https://i.stack.imgur.com/KWXzE.png" rel="nofollow noreferrer">Expected output</a></p>