# Getting a specific row value in a column

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

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;
``````
Similar Questions

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
``````

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....

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

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?