title: Spark Scala: How to Pass column value from one table as column condition another Dataframe Creation
body: <p>I have a use case like this - I have a look-up table which contains formula and original table contains columns values and the final table need to create with formula and columns value from original table. For each client , formula will get changed .</p>
  <p><img src="https://i.stack.imgur.com/AiYkF.png" alt="Lookup, original and final data frame which need to be created " /></p>
  <pre><code>lkp1:
                  
  |clnt_id    | total_amount     |    total_avg
  ==============================================      
  |1          | col+col2         | col2-col1      
  |2          | col+col2+5       | 1      
  |3          | 2                |  14/col3   
  
  
              
  orig_1              
  clnt_id |name   |col1   |col2   |col3
  1       |name1  |1      |2      |4
  2       |name2  |1      |4      |5
  3       |name4  |3      |5      |7
  
  
  final_1             
  clnt_id |name   |Amount |avg    
  1       |name1  |3      |-2 
  2       |name2  |10     |1  
  3       |name4  |2      |2  
  </code></pre>
  <p>I have achieved the same by using :</p>
  <pre><code>var final_1:DataFrame=_
  var final_intermediate:DataFrame=_
  var cnt=0
  
  val lookup_1_df=spark.sql(&quot;select * from lookup_1&quot;)
  
  var lookup_1_Df=lookup_1.
  select(column(&quot;toatl_amount&quot;),
         column(&quot;total_avg&quot;)
         .collect
  
  
  val lookup_1_Df_length=lookup_1_Df.length
  for (row &lt;- 0 to lookup_1_Df_length-1)
  {
       var  toatl_amount= lookup_1_Df(row)(0).toString
       var  total_avg= lookup_1_Df(row)(1).toString
  
      
       var fina_df_frame= &quot;select clnd_id,name,$toatl_amount,$total_avg from orig_table_1 a  left join lookup_1 b on a.clin_id=b.clnt_id where a.clin_id='$clin_id&quot;
  
       var fina_df_frame_replaced=fina_df_frame.replace(&quot;$clin_id&quot;, clin_id).replace(&quot;$toatl_amount&quot;, toatl_amount).replace(&quot;$total_avg&quot;, total_avg)
       
       final_intermediate=spark.sql(sqlText=fina_df_frame_replaced)
       
       if (cnt == 0)
          {
              final_1=final_intermediate
          }
          
          else
          {
              
             final_1=final_intermediate.union(final_1)  
          }
          
          cnt=cnt+1
  }
  
  final_1.createOrReplaceTempView(&quot;final_1_table&quot;)
  </code></pre>
  <p>Here I have shown sample data set only, my original table contains millions of records and I have 1000+ clients. Hence the looping is not an optimal solution as for each client, the above code snippet has to run which I am aware of.  Can we do it in a more efficient way? Any suggestion?</p>