Two Dates in same line

[chandra63]

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

[rubenmarin · Accepted solution]

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.