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

[jblomqvist]

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?

[sunny_talwar]

Try this:

Table:

LOAD AllYourFields,

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

FROM Source;

[sunny_talwar]

Or this:

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

[settu_periasamy]

I thought like this. 🙂

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

[trdandamudi]

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

[sunny_talwar]

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

[settu_periasamy]

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

[jblomqvist]

Thank you guys

[jblomqvist]

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?

[sunny_talwar]

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;