How to manipulate an straight table to get desired output with given data ?

[shekhar_analyti]

Hi All ,

How to manipulate an straight table to get desired output with given data ?

Data :

LOAD * INLINE [

VENDOR, Payment2018, PAYMENT 2019, PAYMENT_DED_2018, PAYMENT_OVERHEAD, CREDIT2018, CREDIT2019, CREDIT_BOUNCE, CREDIT_RATE, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8
VENDOR1, 0.014903551, 0.54727727, 0.743786102, 0.537587997, 0.286282171, 0.078097565, 0.587196829, 6, 0.169619538, 0.946837744, 0.71361757, 0.255027295, 0.898727864, 0.446281594, 0.845183876, 0.467796974
VENDOR2, 0.719555082, 0.889370742, 0.662853046, 0.173661065, 0.595022152, 0.811257423, 0.821658062, 8, 0.574746515, 0.502017105, 0.39527075, 0.34581875, 0.839653611, 0.699575373, 0.258004394, 0.161535118
VENDOR3, 0.621144086, 0.571040709, 0.434802076, 0.448548116, 0.231406287, 0.849085823, 0.199355075, 9, 0.792294151, 0.676985581, 0.452453542, 0.660418761, 0.266702294, 0.767286077, 0.569414714, 0.211049033
VENDOR4, 0.191851842, 0.029283145, 0.730212747, 0.705538596, 0.79331519, 0.64622737, 0.171338897, 4, 0.859663977, 0.32344068, 0.616189235, 0.129547762, 0.748596874, 0.829644056, 0.360888262, 0.268095577
VENDOR5, 0.655438132, 0.872745003, 0.95785293, 0.802078008, 0.036580051, 0.8904356, 0.723257401, 5, 0.840242034, 0.91035259, 0.781432209, 0.264124405, 0.306491063, 0.291127477, 0.45869862, 0.406935513
VENDOR6, 0.393480538, 0.199055413, 0.621213912, 0.535477306, 0.291131634, 0.346817971, 0.239241541, 6, 0.300755703, 0.627458231, 0.203518769, 0.489155922, 0.839126598, 0.027308656, 0.222748241, 0.526150643
VENDOR7, 0.755687577, 0.329914825, 0.611604071, 0.455403337, 0.769138138, 0.199948614, 0.864610239, 7, 0.578003958, 0.996715012, 0.707244019, 0.155479802, 0.072983675, 0.235913826, 0.563814177, 0.632184752
VENDOR8, 0.681203075, 0.230726282, 0.86446857, 0.111979868, 0.987261283, 0.455826836, 0.359252262, 4, 0.820872696, 0.467111551, 0.493800888, 0.8346522, 0.111688304, 0.856499434, 0.080661351, 0.568003103
];

Input :
 output 

Thanks & Regards

SHEKAR

[rahulpawarb]

Steps to be followed:

1. Create a dimension

      =ValueList('Payment', 'CREDIT')

2. Add below expressions

     //2018

     =If(ValueList('Payment', 'CREDIT')='Payment', Sum(Payment2018), Sum(CREDIT2018))

     //2019

     =If(ValueList('Payment', 'CREDIT')='Payment', Sum([PAYMENT 2019]), Sum(CREDIT2019))

     //LYD

     =If(ValueList('Payment', 'CREDIT')='Payment', Sum(PAYMENT_DED_2018), 0)

Hope this will help!

-Rahul