Conditional Load Statement

[jleefjcapital]

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]);

[volakakis]

something like this maybe? Formatting Quarter and Year - sorting

[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

[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,

[jleefjcapital]

I think there's something missing in the syntax.  The code won't load. 

[jleefjcapital]

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/Da... 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;

[marcus_sommer]

How looked the error-message?

- Marcus

[jleefjcapital]

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

[jleefjcapital]

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

[shubham_singh]

I missed writing mapping load in quarter map, you will not error get after that.

If you have master calender in your data model, just create date field and skip MonthYear and QuarterYear