top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Getting a specific row value in a column

0 votes
58 views

I have data for current year as well as next year in a table.

Sample Table data:

Year,Period,prd_Desc,Amount
2014,11,    Humira,  120
2015,11,    humira,  140
Key Coulmn are Year,Period,prd_Desc

If the data present for next year and for same period, i need that value in a separate column. Like below

Year,Period,prd_Desc,Amount_curnt_yr,Amount_next_yr
2014,11,    humira,  120,            140

I can achive this by doint a left outer join between same table using below query:

select a.Year,a.Period,a.prd_Desc,a.Amount as Amount_curnt_yr,b.Amount as Amount_next_yr 
from (select Year,Period,prd_Desc,Amount 
      from tableA) a 

left outer join (select Year,Period,prd_Desc,Amount from tableA) b on
b.year=a.year+1 and a.Period=b.period and a.prd_Desc=b.prd_Desc
I was trying to get it in a simngle query without using left outer join, but could not. If anybody can share any idea, that would helps

posted Jan 30, 2015 by Sachin

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Assuming your real table has some more rows (i.e. more years and more periods) than you show us, this would be a more generic solution:

WITH t AS   
    (SELECT YEAR,Period,prd_Desc,Amount, 
       LEAD(amount, 1) OVER (PARTITION BY prd_Desc, period ORDER BY YEAR) AS amount_next_year 
    FROM a_table)
SELECT * 
FROM t
WHERE amount_next_year IS NOT NULL;

In case values in column YEAR are not continuous, you can do it like this:

WITH t AS   
    (SELECT YEAR,Period,prd_Desc,Amount, 
        LAST_VALUE(amount) OVER 
        (PARTITION BY period, prd_Desc ORDER BY YEAR 
            RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS amount_next_year 
    FROM A_TABLE)
SELECT * 
FROM t
WHERE amount_next_year IS NOT NULL;
answer Feb 4, 2015 by Shweta Singh
Similar Questions
0 votes

My source data

eno  |  ename   |   sal
1       AAA         1000
2       BBB         2000
3       ccc         3000
4       DDD         4000

I want output is

eno  |  ename   |   sal
1       AAA         1000
2       BBB         3000
3       ccc         5000
4       DDD         7000

Can you please say this answer.

0 votes

I have the following table with the shown data in it:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456
11-15-2014 | 789

I need to do 2 things:

1) Calculate the max value for send_date

2) Filter out any rows whose send_date does not match that value

In other words, I want the output to be:

send_date | household_ID

11-20-2014 | 123
11-20-2014 | 456

Row number 3 should be filtered out as its send_date is not the max.

I tried creating an aggregate, grouping by all columns, and creating a new output port called MAX_DATE with an expression of MAX(SEND_DATE), then have a filter transformation with the condition MAX_DATE = SEND_DATE

This lets all rows through, though. What can I do to make this work....

0 votes

Produce the design approach to generate the below sample target data requirement from the sample source data.

SRC FILE1 TGT FILE
Col1 Col2 Col1 Col2
101 A 101 CBA
101 B 102 D
101 C 103 E
102 D 104 F
103 E 105 HG
104 F
105 G
105 H

please suggest me how to get the target file answer?

0 votes

The data in the source table is as follows: -

Empno   Ename  sal
101     Allen  1000
102     Alex   2000
103     Tom    1500
104     Cb     2100

I want the output to be as follows :-

Empno   Ename  sal
101     Allen  0
102     Alex   1000
103     tom    2000
104     Cb     1500

Can you please tell me by using which transformation i can acheive the result. Also plz let me know the logic

+1 vote

I have look up T/F in the mapping. If the look up t/f fails to return a row based on the look up condition, i need to send the source record to Bad file. How can i do that?

...