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

[shekhar_analyti]

Hi All ,

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

Input :

OUTPUT 

[shekhar_analyti]

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
];

[sunny_talwar]

Easy way is to crosstable load your data to make it more user friendly. Not so easy way is to use synthetic dimensions and bunch of if statements

[shekhar_analyti]

Thank you Sunny Bhai ...

Hmm ... CrossTable on record with 20,000,00 rows .. It will inflate badly ..     

[sunny_talwar]

The other way might not give you a huge performance boost because what you were doing in the script is now necessarily done on the front end. I can share how this can be done on the front end and you can test it out

[sunny_talwar · Accepted solution]

Something like this?

[shekhar_analyti]

Thank you Bhai .. Let me try !!

Guess what , i have list of 8 synthetic dimensions apart from 2 shared here for sample ... 

So i have something that has challenges "both ways" ...