Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try this:
Table:
LOAD AllYourFields,
MakeDate(2013, Ceil(Rand() * 12), Ceil(Rand() * 29)) as Date
FROM Source;
Or this:
Date(MakeDate(2013) + Ceil(Rand() * 365)) as Date
I thought like this. 🙂
LOAD Date(42370+(ceil(Rand()*365))) as Date AutoGenerate 1 While IterNo()<=365;
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
I think OP needs to add this random date to already existing table, I doubt a AutoGenerate is needed here
yes i got that. Just wanted to give working sample.
Thank you guys
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?
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;