9 Replies Latest reply: Dec 10, 2016 2:38 AM by Shubham Singh RSS

    Conditional Load Statement

    Jes Lee

      Hello,

       

      Is it possible to script a conditional load statement in Qlik Sense?  I'm working with an XML data set that contains both quarterly and monthly data flows.  I would like to load the data format differently for each.  In the original data file, yr is Year, and period is either the numerical month (1-12) or quarter (1-4).   There is a separate field called Frequency (Monthly, Quarterly). 

       

      For monthly, I would use:     Date(Makedate(yr,right(period,2)), 'MMMYYYY') as MONTHYEAR,

      For quarterly, I would use:     Date(Makedate(yr,right(period,2)), 'QYYYY') as QUARTERYEAR,  <----- Is this the correct format? 



      What would be the best way to conditionally load the different dates?  


      Thank you in advance. 

       

      HPI:

      LOAD if (frequency = monthly)

          Date(Makedate(yr,right(period,2)), 'M/D/YYYY') as DATE,

          Date(Makedate(yr,right(period,2)), 'MMMYYYY') as MONTHYEAR,

          hpi_type,

          hpi_flavor,

          frequency,

          level as place_type,

          place_name,

          place_id,

          yr,

          period,

          index_nsa as HPI_nsa,

          index_sa as HPI_sa

      FROM [lib://FHFA/HPI_master.xml]

      (XmlSimple, table is [rows/row]);

        • Re: Conditional Load Statement
          Marcus Sommer

          You could try it in this way:

           

          HPI:

          LOAD

              if (frequency = 'monthly'),

                   Date(Makedate(yr,period), 'M/D/YYYY'),

                   Date(Makedate(yr,period*3-2), 'M/D/YYYY')) as DATE,

              if (frequency = 'monthly'),

                   Date(Makedate(yr,period), 'MMM/YYYY'),

                   Date(Makedate(yr,period*3-2), 'MMM/YYYY')) as MONTHYEAR,

              if (frequency = 'monthly'),

                   ceil(period/3) & '-' & yr,

                   period & '-' & yr) as QUARTERYEAR,

              hpi_type,

              hpi_flavor,

              frequency,

              level as place_type,

              place_name,

              place_id,

              yr,

              period,

              index_nsa as HPI_nsa,

              index_sa as HPI_sa

          FROM [lib://FHFA/HPI_master.xml]

          (XmlSimple, table is [rows/row]);

           

          - Marcus

          • Re: Conditional Load Statement
            Shubham Singh

            Do not use a date function to create MonthYear using Date function will show same month multiple times in UI. You will not see difference here because you have only one date per period i.e. '01'.

             

            Use following code:

             

            MonthMap:

            Mapping

            LOAD * INLINE [

            01,Jan

            02,Feb

            .

            .

            12,Dec

            ];

             

            QuarterMap:

            Mapping

            LOAD * INLINE [

            01,JFM

            02,AMJ

            03,JAS

            04,OND

            ];

             

            MonthQuarterMap:

            Mapping

            LOAD * INLINE [

            01,AMJ

            02,AMJ

            .

            .

            12,OND

            ];

             

            //now in your table load script

            //All else conditions are for Quarterly

             

            if(Frequency='Monthly',

                 ApplyMap('MonthMap',right(period,2)),

                 ApplyMap('QuarterMap',right(period,2))

               )

            &'-'&yr as MonthYear,


            if(Frequency='Monthly',

                 ApplyMap('MonthQuarterMap',right(period,2)),

                 ApplyMap('QuarterMap',right(period,2))

               )

            &'-'&yr as QuarterYear,

             

            if(Frequency='Monthly',

                 Makedate(yr,right(period,2)),

                 Makedate(yr,((right(period,2)-1)*3)+1)

               ) as Date,

              • Re: Conditional Load Statement
                Jes Lee

                What if I'm already using a Master Calendar--- how should I use it together? 

                 

                // //////////// INSTRUCTIONAL VIDEO:  https://community.qlik.com/docs/DOC-8642  ///////////////////////////////////

                // ///////////  DATE TIME FUNCTIONS: HELP 

                // //<http://help.qlik.com/sense/en-US/online/#../Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/DateAndTimeFunctions.htm?Highlight=time functions>////  

                // // This code creates the master calendar to standardize all time and date variables. ///

                 

                 

                QuartersMap:

                MAPPING LOAD

                rowno() AS Month,

                'Q' & Ceil (rowno()/3) AS Quarter

                AUTOGENERATE (12);

                 

                 

                Temp:

                LOAD

                min(DATE) AS minDate,

                max(DATE) AS maxDate

                Resident HPI_Quarterly;

                 

                 

                Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

                Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                DROP Table Temp;

                 

                 

                TempCalendar:

                LOAD

                $(varMinDate) + Iterno()-1 As Num,

                DATE($(varMinDate) + IterNo()-1) AS TempDate

                AutoGenerate 1 While ($(varMinDate) + IterNo() -1) <= $(varMaxDate);

                 

                 

                MasterCalendar:

                LOAD

                    TempDate AS DATE,

                    Week(TempDate) AS Week,

                    Year(TempDate) AS Year,

                    Month(TempDate) AS Month,

                    Year(TempDate)*100+Month(TempDate) AS MonthYear,

                    Day(TempDate) AS Day,

                    ApplyMap('QuartersMap', month(TempDate), Null()) AS Quarter,

                    Year(TempDate)&ApplyMap('QuartersMap', month(TempDate), Null()) AS QuarterYear,

                    Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

                    WeekDay(TempDate) AS WeekDay

                Resident TempCalendar

                Order By TempDate ASC;

                Drop Table TempCalendar;

                 

                 

                //////////////////////////////////////////////////////////////////  CREATION OF AS-OF TABLE ////////////////////////////////////////////////////

                 

                 

                tmpAsOfCalendar:

                Load distinct Month

                Resident MasterCalendar ;

                 

                 

                Join (tmpAsOfCalendar)

                Load Month as AsOfMonth

                Resident tmpAsOfCalendar ;

                 

                 

                [As-Of Calendar]:

                Load Month,

                  AsOfMonth,

                  Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,

                  Year(AsOfMonth)-Year(Month) as YearDiff

                  Resident tmpAsOfCalendar

                      Where AsOfMonth >= Month;

                 

                 

                Drop Table tmpAsOfCalendar;

                • Re: Conditional Load Statement
                  Jes Lee

                  Do I have to explicitly include "all else" in the data statement? 

                  • Re: Conditional Load Statement
                    Jes Lee

                    I tried it this way, and I get an error message stating that Map ID is not found.