topic Re: How to manipulate an straight table set expression to get desired output with given data ? in QlikView

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

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

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

Input :

OUTPUT

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

shekhar_analyti — Thu, 07 Feb 2019 14:59:51 GMT

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

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

sunny_talwar — Thu, 07 Feb 2019 15:02:01 GMT

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

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

shekhar_analyti — Thu, 07 Feb 2019 15:05:47 GMT

Thank you Sunny Bhai ...

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

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

sunny_talwar — Thu, 07 Feb 2019 15:09:05 GMT

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

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

sunny_talwar — Thu, 07 Feb 2019 15:14:47 GMT

Something like this?

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

shekhar_analyti — Thu, 07 Feb 2019 15:16:09 GMT

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