topic Re: date problem in QlikView

date problem

sagarrahul — Tue, 23 Feb 2016 05:27:16 GMT

there is a covernote number & Pol_issue date column and want to get data as in below table i want if pol_issue date is not in order its should gv me its post and pre date of its and also its covernote number .

covernote num	pol_issue date
1	1\1\2016
2	2\1\2016
3	4\1\2016
4	3\1\2016
5	5\1\2016

answer should be liker dis :

covernote num	pol_issue date

	2\1\2016
3	4\1\2016
	6\1\2016

for all data .

pls help in dis as soon as possible as i m new in qlikview .

Re: date problem

hic — Tue, 23 Feb 2016 12:43:08 GMT

I am not sure what output you want, but if you use the following script

Load
If([pol_issue date]<Peek([pol_issue date]),

'Not in order','In order') as Condition,
*;
Load
[covernote num],
Date#([pol_issue date],'D\M\YYYY') as [pol_issue date]

From …

you can get a table like

All you need to do is to alter the If()-function in the script to return what you want.

HIC

Re: date problem

sagarrahul — Wed, 24 Feb 2016 05:17:48 GMT

thanx for ur time and answer bt sir i want the ans sholud be in dis form

covernote num	pol_issue date
1	1\1\2016
2	2\1\2016
3	4\1\2016
4	3\1\2016
5	5\1\2016

covernote num	pol_issue date

	2\1\2016
3	4\1\2016
	3\1\2016

Re: date problem

sagarrahul — Wed, 24 Feb 2016 05:18:51 GMT

thanx for ur time and answer bt sir i want the ans sholud be in dis form

covernote num	pol_issue date
1	1\1\2016
2	2\1\2016
3	4\1\2016
4	3\1\2016
5	5\1\2016

covernote num	pol_issue date

	2\1\2016
3	4\1\2016
	3\1\2016

Re: date problem

sagarrahul — Wed, 24 Feb 2016 09:47:38 GMT

sir how can 4\1\2016 be in order thanx for ur ans bt i want it in dis way

covernote num	pol_issue date

	2\1\2016
3	4\1\2016
	3\1\2016

Re: date problem

hic — Wed, 24 Feb 2016 11:09:09 GMT

Record 1-3 are in order. Record 4 is the first record that is in the wrong position.

Try the following:

FirstTable:
Load
   RecNo() as RecordNumber,
   If(NotInOrder or Peek(NotInOrder),True(),False()) as KeepRecord,
   *;
Load
   If([pol_issue date]<

      Peek([pol_issue date]), True(), False()) as NotInOrder,
    *;
Load
   [covernote num],
   Date#([pol_issue date],'D\M\YYYY') as [pol_issue date]
   From …;

SecondTable:
Load
   RecordNumber,
   [covernote num],
   [pol_issue date]
   Resident FirstTable
   Where KeepRecord or Previous(KeepRecord)
   Order By RecordNumber Desc;

Drop Table FirstTable;

HIC

Re: date problem

sagarrahul — Wed, 24 Feb 2016 11:19:32 GMT

ty so much sir . wil jst check it out

Re: date problem

sagarrahul — Wed, 24 Feb 2016 11:21:13 GMT

sir date is not in proper order na tht is ascending order

Re: date problem

sagarrahul — Wed, 24 Feb 2016 11:26:46 GMT

sir if i m having a application then how to do it can u tell me .

Re: date problem

sagarrahul — Thu, 25 Feb 2016 05:19:03 GMT

can you give expression for this .

covernote num	pol_issue date
1	1\1\2016
2	2\1\2016
3	4\1\2016
4	3\1\2016
5	5\1\2016

covernote num	pol_issue date

	2\1\2016
3	4\1\2016
	3\1\2016

Re: date problem

sagarrahul — Fri, 26 Feb 2016 06:20:46 GMT

hiii there was query i have two data that is covernote number and pol_issue_date

as we see pol_issue_date are in not order .

COVERNOTE_NUMBER	POL_ISSUE_DATE
2302158715	31-Jul-2014
2302158717	11-Aug-2014
2302158722	07-Aug-2014
2302158724	15-Aug-2014
2302158726	12-Oct-2014
2302158727	12-Oct-2014
2302158731	25-Jul-2014
2302158733	28-Jul-2014
2302158736	11-Aug-2014
2302158742	30-Jul-2014
2302158746	16-Sep-2014
2302158747	11-Aug-2014
2302158753	31-Jul-2014
2302158755	31-Jul-2014
2302158758	31-Jul-2014

i want i should get dis output when dates are not in proper order or ascending order .

all the data . i have three yr data and some covernote number .

13-14

14-15

15-16

COVERNOTE_NUMBER	POL_ISSUE_DATE
	31-Jul-2014
2302158717	11-Aug-2014
	07-Aug-2014

it can be done by sort -- ascending bt not in this way .

so pls help me in dis

Re: date problem

sagarrahul — Mon, 29 Feb 2016 04:07:07 GMT

pls help me as soon as possible

Re: date problem

sagarrahul — Wed, 02 Mar 2016 12:03:58 GMT

COVERNOTE_NUMBER	POL_ISSUE_DATE
2302205741	23-Oct-2015
2302205871	18-Dec-2015
2302205871
2302205979	03-Dec-2015
2302205988	03-Nov-2015
2302205991	20-Oct-2015
2302206030	26-Oct-2015
2302206053	19-Nov-2015
2302206061	07-Dec-2015
2302206073	28-Nov-2015
2302206081	14-Nov-2015
2302206081
2302206082	17-Nov-2015
2302206103	19-Nov-2015
2302206185	31-Oct-2015
2302206210	21-Oct-2015
2302206210
2302206211	20-Oct-2015
2302206257	07-Nov-2015
2302206371	14-Oct-2015
2302206374	20-Oct-2015
2302206378	31-Oct-2015
2302206381	24-Oct-2015
2302206407	31-Oct-2015
2302206463	08-Dec-2015
2302206704	28-Oct-2015
2302206705	22-Oct-2015
2302206716	04-Nov-2015
2302206716
2302206718	15-Oct-2015
2302206734	30-Oct-2015
2302206735	26-Oct-2015
2302206739	20-Nov-2015
2302206747	04-Dec-2015
2302206749	21-Dec-2015
2302206792	28-Nov-2015
2302206800	10-Dec-2015
2302206905	20-Nov-2015
2302206938	23-Oct-2015
2302206942	29-Oct-2015
2302206952	15-Oct-2015
2302206981	20-Oct-2015
2302206990	04-Nov-2015
2302207009	03-Nov-2015
2302207015	30-Oct-2015
2302207058	09-Oct-2015
2302207063	04-Nov-2015
2302207081	30-Oct-2015
2302207084	06-Nov-2015
2302207086	14-Dec-2015
2302207115	27-Oct-2015
2302207116	27-Oct-2015
2302207120	28-Oct-2015
2302207142	12-Oct-2015
2302207154	27-Oct-2015
2302207161	24-Oct-2015
2302207163	27-Oct-2015
2302207174	19-Nov-2015
2302207240	28-Oct-2015
2302207251	31-Oct-2015
2302207302	28-Oct-2015
2302207303	21-Oct-2015
2302207306	30-Oct-2015
2302207308	28-Oct-2015
2302207433	17-Oct-2015
2302207441	24-Oct-2015
2302207462	26-Oct-2015
2302207583	29-Oct-2015
2302207586	03-Nov-2015
2302207702	13-Nov-2015
2302207806	20-Nov-2015
2302207832	28-Nov-2015
2302207839	30-Nov-2015
2302207854	30-Dec-2015
2302207864	31-Dec-2015
2302207890	08-Jan-2016
2302208139	25-Nov-2015
2302208153	20-Nov-2015
2302208154	17-Nov-2015
2302208156	18-Nov-2015
2302208160	29-Nov-2015
2302208238	15-Dec-2015
2302208315	10-Dec-2015
2302208367	08-Dec-2015
2302208392	14-Dec-2015
2302208395	14-Dec-2015
2302208399	26-Dec-2015
2302208508	26-Nov-2015
2302208531	13-Dec-2015
2302208531
2302208550	10-Dec-2015
2302208613	11-Dec-2015
2302208804	02-Nov-2015
2302208872	05-Jan-2016
2302209096	25-Nov-2015
2302209231	09-Nov-2015
2302209245	14-Nov-2015
2302209257	30-Nov-2015
2302209283	08-Nov-2015
2302209298	19-Nov-2015
2302209298
2302209348	03-Dec-2015
2302209369	25-Nov-2015
2302209379	19-Nov-2015
2302209602	29-Nov-2015
2302209610	10-Dec-2015
2302209618	27-Dec-2015
2302209731	09-Nov-2015
2302209741	24-Oct-2015
2302209771	31-Dec-2015
2302209979	24-Dec-2015
2302209998	16-Dec-2015
2302210154	30-Nov-2015
2302210161	17-Nov-2015
2302210201	05-Nov-2015
2302210212	07-Nov-2015
2302210217	26-Nov-2015
2302210371	31-Dec-2015
2302210374	29-Dec-2015
2302210516	21-Nov-2015
2302210589	17-Nov-2015
2302210612	02-Dec-2015
2302210649	16-Dec-2015
2302210986	24-Dec-2015
2302210986
2302211038	09-Jan-2016
2302211069	05-Jan-2016
2302211093	08-Dec-2015
2302211255	30-Dec-2015

i m having this table in qlikview application

i am building a rule

rule is :

1) covernote_number should be there .

2) when we see pol_issue_date when date is not in order then it should through its pre date and post date .

output should be in this way

COVERNOTE_NUMBER	POL_ISSUE_DATE
	23-Oct-2015
2302205871	18-Dec-2015

	03-Dec-2015
2302205988	03-Nov-2015
	20-Oct-2015

as on till the end column .

help me as soon as possible