2 Replies Latest reply: Apr 19, 2018 9:10 AM by Alexander James Tomlins RSS

    Generating Dates from min and max date issue.

    Alexander James Tomlins

      Hi guys,

       

      As i'm learning Qlik sense i thought I would try and auto generate a calendar for fun and to see if i could. 

       

      The issue i'm having is when i run this very simple script it seems to get stuck in a loop and generate millions of rows. I don't just want a fix if that's OK - I would ideally like to know what is wrong , why and potential ways of solving the issues.

       

      Thank you ever so much in advance - this community is fantastic and hope that i could one day get good enough to help others as i have been helped!

       

      Thanks,

       

      Alex 

        • Re: Generating Dates from min and max date issue.
          Sunny Talwar

          Would you be able to share your script here?

          • Re: Generating Dates from min and max date issue.
            Alexander James Tomlins

            I've got it working lol I keep fixing these things after i ask them! This is what i have now - but i don't think it's the best way of doing it Please let me know if there's something i should do because i think i have too many tables. I have a master calendar script. I just wanted to see if i could build a daily one myself - just for practice/training and to understand things like rowno iterno etc.  I think my first issue was that i was trying to use a field i was trying to generate in a load statement in the same load statement - which i think isn't possible

             

             

            Temp:

            LOAD

            MinDate as MinDate,

            today() as MaxDate

            INLINE [

            MinDate

            09/04/1988

            ]

            ;


            Let vStartDate = num(Peek('MinDate', '0', 'Temp'));

            Let vEndDate = num(Peek('MaxDate', '0', 'Temp'));

            Let vNumberOfDays = $(vEndDate) - $(vStartDate);


            Table2:

            Load

            -1 + IterNo() as [num]

            Autogenerate 1 WHILE -1 + IterNo() <= $(vNumberOfDays);


            Table3:

            Load

            date($(vStartDate) + num)as [tempdate]

            Resident Table2;


            MasterCalendar:

            Load

            tempdate as Date,

            MONTH(tempdate) as Month

            ,WEEK(tempdate) as Week

            ,WeekDay(tempdate) as WeekDay

            ,MonthName(tempdate) as MonthName

            ,year(tempdate) as Year

            ,WeekName(tempdate) as WeekName

            ,WeekStart(tempdate) as WeekStart

            resident Table3;


            Drop table Temp;

            Drop table Table2;

            Drop table Table3;