Alternate to Generic Load

[Anonymous]

Hi Team,

I want to transpose rows to columns and assign column names dynamically .

It works with the Generic load,but creates n number of joins which may impact on performance.

Is there any alternative solution to achieve the same?

Thanks,

Subhashree R

[Anil_Babu_Samineni]

Can you show the script for 2 Join tables?

[Anonymous]

Hi Anil,

I want to transpose the rows to columns (i.e.)Hour field should be represented as columns

where  their  corresponding usage and Revenue values below to that .Please refer the below script used,

InputTable:

LOAD * INLINE [

    Date,Hour,usage,Revenue

    01-01-2017,H0,100,20

    01-01-2017,H1,100,40

    02-01-2017,H2,100,50

    02-01-2017,H3,100,100

    02-01-2017,H0,400,100

    01-01-2017,H4,100,100];

GenTable:

Generic Load Date,Hour&'_usage', usage Resident InputTable;

GenTable2:

Generic Load Date,Hour&'_Revenue', Revenue Resident InputTable;

I can able to achieve this functionality through Generic Load which creates N number of tables that linked to original tables as below:

Result:

Date	H0_Revenue	H0_usage	H1_Revenue	H1_usage	H2_Revenue	H2_usage	H3_Revenue	H3_usage	H4_Revenue	H4_usage
1/1/2017	20	100	40	100					100	100
2/1/2017	100	400			50	100	100	100

I'm not sure where it will impact performance or not.

Could you please suggest which is the best approach to achieve this Functionality?

Thanks,

Subhashree R

[Peter_Cammaert]

If you load from external files, you can use the Transpose functions in the Data Load wizard.

[Anonymous]

Hi Peter,

Transpose Function is not working for me in this scenario .

i want to transpose some of the fields to columns and not sure how transpose will apply.

Could you please explain with an example?

Thanks

Subhashree R