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: 
Not applicable

Date match

HI Qlikers,

Please find the attachment.

please take two seperate fields from two sheets(sheet1 -> orderdate, sheet 2-> executiondate)

i have two fields order date and executiondate

when ever i select any order date , then particular month and that particular range has to be highlighted

for eg:

if i select 1-7-2014 then in executiondate 1-10 dates(Available executiondates) has to be highlighted.

if i select 12-7-2014 then in executiondate 12-20 dates(Availabe executiondates) has to be highlighted

if i select 24-7-2014 then in executiondate 24-monthend dates(Availabe executiondates) has to be highlighted

Please give me some script code for this.

Thanks & Regards

Sampath Botla

3 Replies
Raj11
Contributor III
Contributor III

  Hi,

try this code:

Data:

LOAD OrderDate,
ExecutionDate
FROM
treeview.xlsx
(
ooxml, embedded labels, table is Sheet1);

tempoderDate:
Load
Distinct
OrderDate
Resident
Data;

Let vOrderDateCount
=

NoOfRows('tempoderDate');

for

i = 0

to

$(vOrderDateCount)

step 1

Let

vOrderDate =

Peek('OrderDate',

$(i),'tempoderDate');

Let

vdiff =

if(

Floor((

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))-1)/10) = 2,

day(

MonthEnd(

Date('$(vOrderDate)','MM-DD-YYYY'))) - (

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))),(

Floor((

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))-1)/10) + 1) * 10 -(

Day(

Date('$(vOrderDate)','MM-DD-YYYY'))));

Load

OrderDate,

ExecutionDate

As

New_ExecutionDate

Resident
Data

Where

ExecutionDate >= '$(vOrderDate)'

And

ExecutionDate<=

OrderDate + '$(vdiff)'
;


NEXT

amit_saini
Master III
Master III

Sampath,

This may help you:

  1. Calculating day’s b/w dates:

Syntax: LOAD OpenDate,

CloseDate,

CloseDate-OpenDate   AS     DaysOpen,

Floor((CloseDate-OpenDate)/10)*10 & ' to ' & (Floor((CloseDate-OpenDate)/10)*10+9)    AS Range

Output:

OpenDate

CloseDate

DaysOpen

Range

28/02/2012

02/03/2012

3

0 to 9

07/03/2012

15/03/2012

8

0 to 9

01/01/2012

10/01/2012

9

0 to 9

14/01/2012

24/01/2012

10

10 to 19

11/03/2012

24/03/2012

13

10 to 19

28/04/2012

12/05/2012

14

10 to 19

27/01/2012

11/02/2012

15

10 to 19

08/02/2012

23/02/2012

15

10 to 19

07/01/2012

24/01/2012

17

10 to 19

23/04/2012

11/05/2012

18

10 to 19

30/01/2012

19/02/2012

20

20 to 29

12/03/2012

01/04/2012

20

20 to 29

08/02/2012

29/02/2012

21

20 to 29

09/04/2012

01/05/2012

22

20 to 29

17/02/2012

11/03/2012

23

20 to 29

08/04/2012

03/05/2012

25

20 to 29

24/02/2012

07/04/2012

43

40 to 49

Raj11
Contributor III
Contributor III

Check this file