Dynamic vertical pivot issue

[kumarjit]

Hi.

My input is as follows:

Code: Select all

account#	currency	       category            fee amount
1001	        USD	        Investment_Retail	100
1001	        CAD	        Equity              200
1001	        CHY	        Group_Liquidation	300
1002	        USD	        Investment_Retail	400
1002	        CHY	        Group_Liquidation	500

The output expected is:

Code: Select all

account#	currency	Investment_Retail     Equity        Group_Liquidation
1001	        USD  	100                    	0       	  0
1001	        CAD  	  0                     200    	     0
1001	        CHY       0                       0       	300
1002	        USD  	400                    	0       	  0
1002           CHY       0                       0       	500

The logic is:
1. There can be 3 values of the category column at max
2. If for any account#-currency combination there is no fee amount for a specific category, then it would be defaulted to 0 (zero) in the corresponding category column in the output.

For example, for account#=1001 and currency=CAD, there is no record in the input with category="Investment_Retail", hence the corresponding output record will have a value of 0 in the column "Investment_Retail".

How can I achieve this?
Please help.

Regards,
Kumarjit.

[info_ds]

Hi,
You could use pivot SQL to achieve the output if you can stage the data in database. Please see the SQL below (SQL Server based):

Code: Select all

select account,currency, isnull(Investment_Retail,0) as Investment_Retail,
isnull(Equity,0) as Equity,isnull(Group_Liquidation,0) as Group_Liquidation
from
(
  select account,currency,category,fee
  from  test_ds
) acc
pivot
(
  max(fee)
  for category in (Investment_Retail,Equity,Group_Liquidation)
) piv 
order by account;

[kumarjit]

@info_ds: Thanks for the suggestion, but the input data is fully file based; we even do not have access to any database from our Datastage jobs.


Any other help is appreciated.


Regards,
Kumarjit.

[chulett]

With the same number of rows going out as coming in, you don't really have a 'vertical pivot' IMHO, you just need to decide what one of several output columns a particular input value needs to go in based on what is in each 'group' it belongs to. Which to me implies a fork join design where one path determines the contents of each group and then the main path joins to it to determine what its group looks like and thus where the value should end up.

[kumarjit]

Thanks Craig.
Will try implementing a Fork Join design, and post on the result.


Regards,
Kumarjit.

[ray.wurlod]

Heck, you don't need any of that fancy stuff. Been over-thinking the problem. Just some plain ol' If..Then.Else constructs will do it easily if there are only the three choices. For example the derivation of Equity would be

Code: Select all

 If InLink.Category = "Equity" Then InLink.Max Else 0

[chulett]

And I suspect you are... under-thinking it.

I got the impression the derivation was dependent on the results across the data set by account and currency, not just what was present on each line.

[ray.wurlod]

My approach achieves the expected output. I couldn't read any further requirement in the SQL provided.

[chulett]

I didn't take the SQL into account either, just going by the phrasing in the original post. A more complete / complex example may have helped. [shrug]

[ray.wurlod]

I guess we need to wait to see how kumarjit reacts.

[kumarjit]

Earnest thanks for all the help. I think that I can get over this, provided that I'm abble to address the issue below.
This is the lone step that's standing between me and success, and I seek all your help on it.

A sample input is:

Code: Select all

account_00      account_01              account_02
1001              1001		
                  1002
1003		        1003		             1003

The output required is:

Code: Select all

account
1001
1002
1003

The logic is:
1.AT LEAST ONE of the input columns will contain account number values, but each of these columns will contain the
SAME account value. Example, as in the first record, the columns account_00 and account_01 contains the same account
number details which is 1001
2.If one of these input columns contain the account number, the output will be the UNIQUE and non blank value of these account number columns.
Example, the third record contains 1003 accross all the input columns, so output for this record will be the Uniqe and non blank value for all
the three input columns which is 1003. Similarly, for the second record, output will be 1002, and for the first will be 1001.

How can this be acheived?
Please help.

Thanks.

Regards,
Kumarjit.

[chulett]

Not really seeing a problem here as it looks like you just choose the first non-empty value of the three and output that. That's just IF-THEN-ELSE. Or am I missing something?

[kumarjit]

You are absolutety rite Craig.
But there are 15 columns that I need to check and get the First non empty value.
And, I am not able to construct the If Then Else block.

Could you please help on that?


Thanks.
Kumarjit.

[chulett]

Well been awhile but something like this, fleshed out:

Code: Select all

IF IsNotNull(Column1) THEN Column1 ELSE IF IsNotNull(Column2) THEN Column2 ELSE IF ...  ELSE Column15

Based on your stated rules you don't even need bother to check the last one, if you get there and haven't found a non-null yet it must be the one.

[kumarjit]

@chulett: Thanks a ton for the If Else construct Craig, and everyone else who had chipped in their thoughts.

Yes. That If Else block suffices.


Warm Regards,
Kumarjit.