topic Re: how to get pre date and post date in QlikView

how to get pre date and post date

sagarrahul — Tue, 01 Mar 2016 05:55:03 GMT

Considerations-

1) covernote Number and Issue Date are coming in-line with Each Other & In Mentioned Sequence.

2) Data Arrives in Ascending Order of Issue_Date

Pseudo-Code

1) Split Issue_Date Column. Capture Month & Date into two Difference Array Variables/Dataset (as Preferred)

date_array1[]= issue_date.split(1)

month_array2[]=issue_date.split(2)

2) Store CovenantNo into another array

covenant_no_array3[]= loop(Covenant Number)

3) Apply Bubble Sort (Quick Sort Can also be done for Faster Results, but Bubble Sort will be easy to identify out of Order Value.)-

a. First on month_array2[] (use as outer loop)

b. Second on date_array1[] (use as inner loop)

4) When out of Order Value found, print that value as Current Value. Pre-Value is index-1 of Current Value. Post Value is index+1 of current value. Covenant number is index of current value,

used in covenant_no array

i.e.

current_value= date_array1

pre_value= date_array1[i-1]

post_value= date_array1[i+1]

covenant_no=covenant_no_array3

how to do it in script and expression

can u help me in dis

Re: how to get pre date and post date

Gysbert_Wassenaar — Tue, 01 Mar 2016 12:18:33 GMT

Can you post a file with example source data and a file with the result data you expect?

Re: how to get pre date and post date

marcus_sommer — Tue, 01 Mar 2016 12:20:00 GMT

Pseudo-code isn't really helpful. Qlikview works (very well) with fields and tables and not with arrays although fields and tables could be considered as arrays in some way. I think you should provide a small example with a few inline-datas and a better description what do you want to do.

- Marcus

Re: how to get pre date and post date

sagarrahul — Tue, 01 Mar 2016 12:31:06 GMT

can u xplain it more

Re: how to get pre date and post date

sagarrahul — Tue, 01 Mar 2016 12:32:45 GMT

i want this

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 n

Re: how to get pre date and post date

marcus_sommer — Tue, 01 Mar 2016 13:58:46 GMT

You could sort your data within a resident load, maybe in this way:

Source:

Load COVERNOTE_NUMBER, POL_ISSUE_DATE From YourSource;

Sorted:

Noconcatenate Load COVERNOTE_NUMBER, POL_ISSUE_DATE Resident Source Order by POL_ISSUE_DATE;

drop table Source;

- Marcus

Re: how to get pre date and post date

sagarrahul — Wed, 02 Mar 2016 04:02:29 GMT

how will i get this order if date is not in the order .

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

Re: how to get pre date and post date

marcus_sommer — Wed, 02 Mar 2016 15:36:57 GMT

What speaks against the sorting?

Re: how to get pre date and post date

sagarrahul — Thu, 03 Mar 2016 03:59:36 GMT

i have two things covernote_num and pol_issue_date

let dis b table from this i want following things

if date is not in order then it should get me pre and post date along with its covernote_number

COVERNOTE_NUMBER	POL_ISSUE_DATE
2302168701	16-Oct-2014
2302168715	29-Nov-2014
2302168717	28-Nov-2014
2302168718	26-Nov-2014
2302168720	28-Nov-2014
2302168721	23-Sep-2014
2302168724	24-Sep-2014
2302168733	10-Oct-2014
2302168758	19-Nov-2014
2302168759	12-Nov-2014
2302168763	18-Oct-2014
2302168768	18-Oct-2014
2302168770	26-Oct-2014
2302168772	28-Oct-2014
2302168776	28-Oct-2014
2302168777	26-Oct-2014
2302168781	20-Dec-2014
2302168783	20-Dec-2014
2302168793	26-Oct-2014
2302168797	27-Nov-2014
2302168798	28-Nov-2014
2302168812	10-Nov-2014
2302168822	18-Nov-2014
2302168842	21-Nov-2014
2302168844	26-Nov-2014
2302168861	04-Dec-2014
2302168896	01-Dec-2014
2302168904	18-Nov-2014
2302168905	18-Nov-2014
2302168908	18-Nov-2014
2302168910	19-Nov-2014
2302168915	20-Nov-2014
2302168931	20-Nov-2014
2302168934	11-Nov-2014
2302168940	21-Nov-2014
2302168945	22-Nov-2014
2302168946	22-Nov-2014
2302168958	04-Oct-2014
2302168971	29-Sep-2014
2302168973	07-Oct-2014
2302168979	09-Dec-2014
2302169001	20-Sep-2014
2302169008	27-Sep-2014
2302169012	07-Oct-2014
2302169014	27-Sep-2014
2302169026	21-Oct-2014
2302169055	30-Oct-2014
2302169062	11-Oct-2014
2302169063	14-Oct-2014

this should be the output for all tables that i want u jst help in dis i wil rest of its

COVERNOTE_NUMBER	POL_ISSUE_DATE
	16-Oct-2014
2302168715	29-Nov-2014
2302168717	28-Nov-2014
2302168718	26-Nov-2014
	28-Nov-2014
2302168721	23-Sep-2014

Re: how to get pre date and post date

marcus_sommer — Thu, 03 Mar 2016 08:34:32 GMT

Sorry, I don't understand what do you want to do and where your problems are.

- Marcus

Re: how to get pre date and post date

Thu, 03 Mar 2016 09:29:41 GMT

HI sagar

you can try this in sorting tab

Sort by expression (Ascending):

=Date#(Day&Month&Year, 'DDMMMYYYY')

Re: how to get pre date and post date

sagarrahul — Thu, 03 Mar 2016 09:39:17 GMT

ty sir for giving ur time

i m having certain covernote_number and pol_issue_date.

as in this table

covernote number	pol_issue_date
2302168904	18-Nov-2014
2302168905	18-Nov-2014
2302168908	18-Nov-2014
2302168910	19-Nov-2014
2302168915	20-Nov-2014
2302168931	20-Nov-2014
2302168934	11-Nov-2014
2302168940	21-Nov-2014
2302168945	22-Nov-2014
2302168946	22-Nov-2014

sir this date table is in proper ascending order .

bt my problem comes when date i.e. pol_issue_date is not in proper order .

will gv u example :

COVERNOTE_NUMBER	POL_ISSUE_DATE
2302168701	16-Oct-2014
2302168715	29-Nov-2014
2302168717	28-Nov-2014
2302168718	26-Nov-2014
2302168720	28-Nov-2014
2302168721	19-Nov-2014

sir look at this :

first covernote_number is 2302168701 and its pol_issue date is 16\oct\2014

(in this both covernote_number and pol_issue_date is correct in sequences )

second covernote_number is 2302168715 and its pol_issue date is 29\nov\2014

(in this covernote_number and pol_issue_date is correct as its upper date is of oct month and this date is nov so it correct )

third covernote_number is 2302168707 and its pol_issue date is 28\nov\2014

(in this covernote_number is right and pol_issue_date is not in order date is 28-nov-2014 from above date is bigger that is 29\nov\2014 month )

as all the rest are in not order

so i want the output in dis way

all the pol_issue date which are not in order it should through this output as below

COVERNOTE_NUMBER	POL_ISSUE_DATE
	29-Nov-2014
2302168717	28-Nov-2014
	26-Nov-2014