Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
sagarrahul
Creator
Creator
‎2016-03-04 04:49 AM

pre and post date if it is not ascending order

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

230213973107-Apr-2014
230213973628-Apr-2014
230213973829-Apr-2014
230213974625-Apr-2014
230213975529-Apr-2014
230213976028-Apr-2014

answer in this way :

 

29-Apr-2014
230213974625-Apr-2014
29-Apr-2014
230213976028-Apr-2014
657 Views
0 Likes
8 Replies
swuehl
MVP
MVP
‎2016-03-04 02:54 PM

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;

466 Views
sagarrahul
Creator
Creator
‎2016-03-07 10:59 PM
Author

In response to swuehl

Thank you for giving your reply with how it works.

466 Views
0 Likes
sagarrahul
Creator
Creator
‎2016-03-11 04:45 AM
Author

In response to sagarrahul

its not working sir swuehl 

a bcn u give output of this

466 Views
0 Likes
swuehl
MVP
MVP
‎2016-03-11 05:27 PM

In response to sagarrahul

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.

466 Views
0 Likes
sagarrahul
Creator
Creator
‎2016-03-15 03:00 AM
Author

COVERNOTE_NUMBERPOL_ISSUE_DATE
230220606107-Dec-2015
230220607328-Nov-2015
230220625707-Nov-2015
230220674921-Dec-2015
230220680010-Dec-2015
230220711627-Oct-2015
230220816029-Nov-2015
230220887205-Jan-2016
230221020105-Nov-2015
230221021207-Nov-2015
230221037429-Dec-2015
230221103809-Jan-2016
230221106905-Jan-2016
230220587118-Dec-2015
2302205871
230220610319-Nov-2015
230220637420-Oct-2015
230220670428-Oct-2015
230220671815-Oct-2015
230220673526-Oct-2015
230220700903-Nov-2015
230220708614-Dec-2015
230220724028-Oct-2015
230220730228-Oct-2015
230220783228-Nov-2015
230220815417-Nov-2015
230220815618-Nov-2015
230220861311-Dec-2015
230220937919-Nov-2015
230220960229-Nov-2015
230220961010-Dec-2015
230220961827-Dec-2015

see i m having this two things that is COVERNOTE_NUMBERand 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_NUMBERand 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
230220607328-Nov-2015
07-Nov-2015

2302206257  07-Nov-2015this 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
230220625707-Nov-2015
21-Nov-2015

230220674921-Dec-2015this 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 .

230220680010-Dec-2015this 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
230220680010-dec-2015
27-dec-2015

230220711627-Oct-2015this 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
230220711627-oct-2015
29-nov-2015

230220816029-Nov-2015as it goes on till it end the last covernote_number .

230220887205-Jan-2016

230221020105-Nov-2015

230221021207-Nov-2015

230221037429-Dec-2015

230221103809-Jan-2016

230221106905-Jan-2016

230220587118-Dec-2015

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

pls help me in this as soon as possblie .

466 Views
0 Likes
sagarrahul
Creator
Creator
‎2016-03-15 03:29 AM
Author

COVERNOTE_NUMBERand POL_ISSUE_DATE

230220606107-Dec-2015 -this is first row COVERNOTE_NUMBER and POL_ISSUE_DATE no problem with date and covernote_num .
230220607328-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


230220607328 Nov 15



7 Nov 15
23022062577 Nov 15this 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


23022062577 Nov 15



21 Nov 15
230220674921 Dec 15this 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 .
230220680010 Dec 15this 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


230220680010 Dec 15



27 Dec 15
230220711627 Oct 15this 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


230220711627 Oct 15



29 Nov 15
230220816029 Nov 15as it goes on till it end the last covernote_number .
23022088725 Jan 16

23022102015 Nov 15

23022102127 Nov 15

230221037429 Dec 15

23022110389 Jan 16

23022110695 Jan 16

230220587118 Dec 15

2302205871
and if COVERNOTE_NUMBER is double then show it as null value ie o
466 Views
0 Likes
sagarrahul
Creator
Creator
‎2016-03-22 03:21 AM
Author

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


466 Views
0 Likes
sagarrahul
Creator
Creator
‎2016-03-22 07:49 AM
Author

In response to sagarrahul

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

466 Views
0 Likes