Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jblomqvist
Contributor II

How can I generate random dates in a date field from a specific year in a table?

Hi all,

How can I generate random dates in a date field from a specific year in a table?

For example I might have a table with 5,436 rows and I would like a date to be generated for each row in a Date field.

The dates have to be from a specific year (i.e. whatever year a user specifies). E.g. 2013.

How is this possible in QlikView please?

12 Replies

Re: How can I generate random dates in a date field from a specific year in a table?

Try this:

Table:

LOAD AllYourFields,

          MakeDate(2013, Ceil(Rand() * 12), Ceil(Rand() * 29)) as Date

FROM Source;

Re: How can I generate random dates in a date field from a specific year in a table?

Or this:

Date(MakeDate(2013) + Ceil(Rand() * 365)) as Date

Re: How can I generate random dates in a date field from a specific year in a table?

I thought like this. :-)

LOAD Date(42370+(ceil(Rand()*365))) as Date AutoGenerate 1 While IterNo()<=365;

trdandamudi
Honored Contributor

Re: How can I generate random dates in a date field from a specific year in a table?

If user wants to enter the year using Input box then may be as below:

Let vYear = Num(Input('Enter Year (YYYY)' , 'Input required'));

LOAD  *,

    Date(MakeDate($(vYear) + Ceil(Rand() * 365)) as Date

Re: How can I generate random dates in a date field from a specific year in a table?

I think OP needs to add this random date to already existing table, I doubt a AutoGenerate is needed here

Re: How can I generate random dates in a date field from a specific year in a table?

yes i got  that. Just wanted to give working sample.

jblomqvist
Contributor II

Re: How can I generate random dates in a date field from a specific year in a table?

Thank you guys

jblomqvist
Contributor II

Re: How can I generate random dates in a date field from a specific year in a table?

Hi Sunny,

Let's say I use your expression like this:

Date(MakeDate(2013) + Ceil(Rand() * 365)) as Submitted_Date


Then what I want to also calculate the paid date:

Date(MakeDate(2013) + Ceil(Rand() * 365)) as Paid_Date

How can I set the Paid_Date to be after Submitted_Date? E.g. Within 15 days or something.

I realise the problem is if Submitted Date is let's say 30th Dec 2013 then the Paid_Date year has to be next year (e.g. 2014).

Any idea how to do this?

Re: How can I generate random dates in a date field from a specific year in a table?

May be this:

Table:

LOAD *,

  Date(Submitted_Date + Ceil(Rand() * 30)) as Paid_Date;

LOAD Date(MakeDate(2013) + Ceil(Rand() * 365)) as Submitted_Date

AutoGenerate 100;

Community Browser