Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chandra63
Creator
Creator

Two Dates in same line

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

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

1 Reply
rubenmarin

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.