Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi EveryOne,
Hope all are doing Good,
please help me with below requirement.
RZDATE RZAm WAVDATE WAVAM
May | 3 | 6118034311 | 15300 | 5/1/2018 | 31-05-2018 | 14800 | | | 3
May | 3 | 6118034311 | 15300 | 5/1/2018 | | | 31-05-2018 | 500 | 3
but the problem is the user wants Waiver Date and waiver Amount in the same lines, like below.
May | 3 | 6118034311 | 15300 | 5/1/2018 | 31-05-2018 | 14800 | 31-05-2018 | 500 | 3
can I get that in the same lines, with any expression,
please help us.
Thanks & Regards,
Chandra
Hi, you can use a 'group by' of equal fields and use some aggregation function to keep the values from each row, ie:
OriginalData:
LOAD * INLINE [
F1 ,F2 ,F3 ,F4 ,F5 ,RZDATE ,RZAm ,WAVDATE ,WAVAM ,F6
May ,3 ,6118034311 ,15300 ,5/1/2018 ,31-05-2018 ,14800 , , ,3
May ,3 ,6118034311 ,15300 ,5/1/2018 , , ,31-05-2018 ,500 ,3
];
Data:
NoConcatenate LOAD F1, F2, F3, F4, F5, F6,
Date#(MaxString(Text(RZDATE)),'DD-MM-YYYY') as RZDATE,
Num#(MaxString(Text(RZAm)),'0') as RZAm,
Date#(MaxString(Text(WAVDATE)),'DD-MM-YYYY') as WAVDATE,
Num#(MaxString(Text(WAVAM)),'0') as WAVAM
Resident OriginalData
Group By F1, F2, F3, F4, F5, F6;
DROP Table OriginalData;
Data table uses Text() to convert all values to a string, then a Maxstring() to keep the value, and outer Date#() or Num#() to restore value format.
Hi, you can use a 'group by' of equal fields and use some aggregation function to keep the values from each row, ie:
OriginalData:
LOAD * INLINE [
F1 ,F2 ,F3 ,F4 ,F5 ,RZDATE ,RZAm ,WAVDATE ,WAVAM ,F6
May ,3 ,6118034311 ,15300 ,5/1/2018 ,31-05-2018 ,14800 , , ,3
May ,3 ,6118034311 ,15300 ,5/1/2018 , , ,31-05-2018 ,500 ,3
];
Data:
NoConcatenate LOAD F1, F2, F3, F4, F5, F6,
Date#(MaxString(Text(RZDATE)),'DD-MM-YYYY') as RZDATE,
Num#(MaxString(Text(RZAm)),'0') as RZAm,
Date#(MaxString(Text(WAVDATE)),'DD-MM-YYYY') as WAVDATE,
Num#(MaxString(Text(WAVAM)),'0') as WAVAM
Resident OriginalData
Group By F1, F2, F3, F4, F5, F6;
DROP Table OriginalData;
Data table uses Text() to convert all values to a string, then a Maxstring() to keep the value, and outer Date#() or Num#() to restore value format.