pre and post date if it is not ascending order - Qlik Community

sagarrahul · ‎2016-03-04

i m having two column that is covernote_num and pol_issue_date

what i want is if date is not in ascending order then is should give me pre and post date of it . sum wht in dis way

able would be in this away

2302139731	07-Apr-2014
2302139736	28-Apr-2014
2302139738	29-Apr-2014
2302139746	25-Apr-2014
2302139755	29-Apr-2014
2302139760	28-Apr-2014

answer in this way :



	29-Apr-2014
2302139746	25-Apr-2014
	29-Apr-2014
2302139760	28-Apr-2014

swuehl · ‎2016-03-04

Not really sure where you want to show the expected result, if you need to create a table like this, try something like

SET DateFormat = 'DD-MM-YYYY';

INPUT:

LOAD * INLINE [

covernote_num, pol_issue_date

2302139731, 07-Apr-2014

2302139736, 28-Apr-2014

2302139738, 29-Apr-2014

2302139746, 25-Apr-2014

2302139755, 29-Apr-2014

2302139760, 28-Apr-2014

];

TMP:

LOAD *, if( Peek(OutOfOrder), 1) as ShowOutofOrder;

LOAD *, if( Previous(pol_issue_date) > pol_issue_date, 1) as OutOfOrder

Resident INPUT order by covernote_num Asc;

TMP2:

NoConcatenate

LOAD covernote_num, pol_issue_date, OutOfOrder, if( Peek(OutOfOrder), 1, ShowOutofOrder) as ShowOutofOrder

Resident TMP order by covernote_num desc;

RESULT:

NoConcatenate

LOAD

If(ShowOutofOrder, '', covernote_num) as covernote_num,

pol_issue_date

Resident TMP2

WHERE RANGESUM(ShowOutofOrder, OutOfOrder)

ORDER BY covernote_num asc;

DROP TABLES TMP,TMP2, INPUT;

sagarrahul · ‎2016-03-07

Thank you for giving your reply with how it works.

sagarrahul · ‎2016-03-11

its not working sir swuehl

a bcn u give output of this

swuehl · ‎2016-03-11

If you want more help, you would describe what exactely the problem is: Describe your setting by posting some sample data or a small sample QVW, describe what you currently get and what you expect to see.

'Isn't working' is not a sufficient error report.

sagarrahul · ‎2016-03-15

COVERNOTE_NUMBER	POL_ISSUE_DATE
2302206061	07-Dec-2015
2302206073	28-Nov-2015
2302206257	07-Nov-2015
2302206749	21-Dec-2015
2302206800	10-Dec-2015
2302207116	27-Oct-2015
2302208160	29-Nov-2015
2302208872	05-Jan-2016
2302210201	05-Nov-2015
2302210212	07-Nov-2015
2302210374	29-Dec-2015
2302211038	09-Jan-2016
2302211069	05-Jan-2016
2302205871	18-Dec-2015
2302205871
2302206103	19-Nov-2015
2302206374	20-Oct-2015
2302206704	28-Oct-2015
2302206718	15-Oct-2015
2302206735	26-Oct-2015
2302207009	03-Nov-2015
2302207086	14-Dec-2015
2302207240	28-Oct-2015
2302207302	28-Oct-2015
2302207832	28-Nov-2015
2302208154	17-Nov-2015
2302208156	18-Nov-2015
2302208613	11-Dec-2015
2302209379	19-Nov-2015
2302209602	29-Nov-2015
2302209610	10-Dec-2015
2302209618	27-Dec-2015

see i m having this two things that is COVERNOTE_NUMBER

and POL_ISSUE_DATE in excel in libreoffice .

i want its pre date post dates and it covernote_num .

both the values that is COVERNOTE_NUMBER and POL_ISSUE_DATE each values are define two each other .

showing wht i want :

example :

COVERNOTE_NUMBER

and POL_ISSUE_DATE

2302206061

07-Dec-2015 -

this is first row COVERNOTE_NUMBER and POL_ISSUE_DATE no problem with date and covernote_num .

2302206073	28-Nov-2015 -	this is second row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this
			row ie date is 07-nov-2015 smaller than previous date ie 28-dec-2015 so date is not in order value .so we want output
			should be like this its give its pre and post date with is covernote_number .

					07-Dec-2015
			2302206073	28-Nov-2015
					07-Nov-2015

2302206257	07-Nov-2015	this is third row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date
			is 07-nov-2015 smaller than previous date ie 28-nov-2015 so date is not in order value .so we want output should be like
			this its give its pre and post date with is covernote_number .

					28-Dec-2015
			2302206257	07-Nov-2015
					21-Nov-2015

2302206749	21-Dec-2015	this is fourth row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date
			is 21-dec-2015 is bigger than previous date ie 07-nov-2015 so date is in order .

2302206800	10-Dec-2015	this is fifth row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this
			row ie date is 10-dec-2015 smaller than previous date ie 21-dec-2015 .so date is not in order value so we want output
			should be like this its give its pre and post date with is covernote_number .

					21-Dec-2015
			2302206800	10-dec-2015
					27-dec-2015

2302207116	27-Oct-2015	this is sixth row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this
			row ie date is 27-oct-2015 smaller than previous date ie 10-dec-2015 .so date is not in order value so we want output
			should be like this its give its pre and post date with is covernote_number .

					10-Dec-2015
			2302207116	27-oct-2015
					29-nov-2015

2302208160

29-Nov-2015

as it goes on till it end the last covernote_number .

2302208872

05-Jan-2016

2302210201

05-Nov-2015

2302210212

07-Nov-2015

2302210374

29-Dec-2015

2302211038

09-Jan-2016

2302211069

05-Jan-2016

2302205871

18-Dec-2015

2302205871

and if COVERNOTE_NUMBER is double then show it as null value ie o

pls help me in this as soon as possblie .

sagarrahul · ‎2016-03-15

COVERNOTE_NUMBER	and POL_ISSUE_DATE
2302206061	07-Dec-2015 -	this is first row COVERNOTE_NUMBER and POL_ISSUE_DATE no problem with date and covernote_num .
2302206073	28-Nov-2015 -	this is second row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 07-nov-2015 smaller than previous date ie 28-dec-2015 so date is not in order value .so we want output should be like this its give its pre and post date with is covernote_number .
			7 Dec 15
		2302206073	28 Nov 15
			7 Nov 15
2302206257	7 Nov 15	this is third row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date is 07-nov-2015 smaller than previous date ie 28-nov-2015 so date is not in order value .so we want output should be like this its give its pre and post date with is covernote_number .
			28 Dec 15
		2302206257	7 Nov 15
			21 Nov 15
2302206749	21 Dec 15	this is fourth row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date is 21-dec-2015 is bigger than previous date ie 07-nov-2015 so date is in order .
2302206800	10 Dec 15	this is fifth row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 10-dec-2015 smaller than previous date ie 21-dec-2015 .so date is not in order value so we want output should be like this its give its pre and post date with is covernote_number .
			21 Dec 15
		2302206800	10 Dec 15
			27 Dec 15
2302207116	27 Oct 15	this is sixth row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 27-oct-2015 smaller than previous date ie 10-dec-2015 .so date is not in order value so we want output should be like this its give its pre and post date with is covernote_number .
			10 Dec 15
		2302207116	27 Oct 15
			29 Nov 15
2302208160	29 Nov 15	as it goes on till it end the last covernote_number .
2302208872	5 Jan 16
2302210201	5 Nov 15
2302210212	7 Nov 15
2302210374	29 Dec 15
2302211038	9 Jan 16
2302211069	5 Jan 16
2302205871	18 Dec 15
2302205871		and if COVERNOTE_NUMBER is double then show it as null value ie o

sagarrahul · ‎2016-03-22

Hiiiii ,

see i m having this two things that is COVERNOTE_NUMBER and POL_ISSUE_DATE

pls do in inline function

COVERNOTE_NUMBER	POL_ISSUE_DATE
2302206061	07-Dec-2015
2302206073	28-Nov-2015
2302206257	07-Nov-2015
2302206749	21-Dec-2015
2302206800	10-Dec-2015
2302207116	27-Oct-2015
2302208160	29-Nov-2015
2302208872	05-Jan-2016
2302210201	05-Nov-2015
2302210212	07-Nov-2015
2302210374	29-Dec-2015
2302211038	09-Jan-2016
2302211069	05-Jan-2016
2302205871	18-Dec-2015
2302205871
2302206103	19-Nov-2015
2302206374	20-Oct-2015
2302206704	28-Oct-2015
2302206718	15-Oct-2015
2302206735	26-Oct-2015
2302207009	03-Nov-2015
2302207086	14-Dec-2015
2302207240	28-Oct-2015
2302207302	28-Oct-2015
2302207832	28-Nov-2015
2302208154	17-Nov-2015
2302208156	18-Nov-2015
2302208613	11-Dec-2015
2302209379	19-Nov-2015
2302209602	29-Nov-2015
2302209610	10-Dec-2015
2302209618	27-Dec-2015

i want its pre date post dates and along with its covernote_num .

showing what i want :

COVERNOTE_NUMBER	POL_ISSUE_DATE	What i want in my output ie covernote_number and predates and post date explanation :	COVERNOTE_NUMBER	Predate and post date
2302206061	07-Dec-2015 -	this is first row COVERNOTE_NUMBER and POL_ISSUE_DATE_no problem with date and covernote_num because its first value in the table .
2302206073	28-Nov-2015	this is second row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 07-nov-2015 smaller than previous date ie 28-dec-2015 so date is not in order value .so we want output should be like this its give its pre and post date with is covernote_number .	2302206073	28-Nov-2015 07-Nov-2015 07-Dec-2015
2302206257	07-Nov-2015	this is third row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date is 07-nov-2015 smaller than previous date ie 28-nov-2015 so date is not in order value .so we want output should be like this its give its pre and post date with is covernote_number	2302206257	28-Dec-2015 07-Nov-2015 21-Nov-2015
2302206749	21-Dec-2015	this is fourth row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date is 21-dec-2015 is bigger than previous date ie 07-nov-2015 so date is in order and it is in proper order
2302206800	10-Dec-2015	this is fifth row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 10-dec-2015 smaller than previous date ie 21-dec-2015 .so date is not in order value so we want output should be like this its give its pre and post date with is covernote_number .	2302206800	21-Dec-2015 10-Dec-2015 27-Dec-2015
2302207116	27-Oct-2015	this is sixth row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 27-oct-2015 smaller than previous date ie 10-dec-2015 .so date is not in order value so we want output should be like this its give its pre and post date with is covernote_number .	2302207116	10-Dec-2015 27-Oct-2015 29-Nov-2015
2302208160	29-Nov-2015	this is seventh row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date is 29-Nov-2015 is bigger than previous date ie 27-Oct-2015 so date is in order and it is in proper order
2302208872	05-Jan-2016	this is eighth row COVERNOTE_NUMBER and POL_ISSUE_DATE where we can see that covernote_num is in ascending order bt date is 05-Jan-2015 is bigger than previous date ie 29-Nov-2015 so date is in order and it is in proper order
2302210201	05-Nov-2015	this is nineth row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 05-Nov-2015 smaller than previous date ie 05-Jan-2016 .so date is not in order value so we want output should be like this its give its pre and post date with is covernote_number .	2302210201	05-Jan-2016 05-Nov-2015 07-Nov-2015
And if cover note_num is repeated then null value should be shown
It should be till end of covernote_num

this what i need in my table .

so please help me in this as soon as possible .

waiting for reply

sagarrahul · ‎2016-03-22

in second row its wrongs return

2302206073

28-Nov-2015

this is second row COVERNOTE_NUMBER and POL_ISSUE_DATE here we can see that covernote_num is in ascending order bt in this row ie date is 28-nov-2015 smaller than previous date ie 07-dec-2015 so date is not in order value .so we want output should be like this its give its pre and post date with is covernote_number .

2302206073

07-Dec-2015

28-Nov-2015

07-Nov-2015