Where clause in Monthly QVD for huge data?

[ganeshreddy]

Hi All,

Below code is working fine to create monthly QVD, but performance is the issue. While comparing counter month (looping month) to the data with Date field its checking the entire data for each loop. Is there any other option like only capture the data for one month and store it and do not check for that month records again in database. Can any one help me on this .

Note: please check highlighted code.

Code:

/*********** Getting Distict Year and Month combinations from temporary calendar  ************/

CALENDAR:

  LOAD

  Date($(vMinDate) + RowNo() - 1) AS tmp_Date

  AUTOGENERATE

  $(vMaxDate) - $(vMinDate) +1;

YEAR_MONTH_CALENDAR:               

  LOAD 

  Distinct Year(tmp_Date)&num(Month(tmp_Date)) as MonthName

  RESIDENT CALENDAR

;

DROP TABLE CALENDAR;

Let vCount=NoOfRows('YEAR_MONTH_CALENDAR');

/**************  Loop through every month which are generated in YEAR_MONTH_CALENDAR to create monthly QVDs****************/

  FOR i=0 to $(vCount)               

  LET vMonth = Peek('MonthName',$(i),'YEAR_MONTH_CALENDAR');

  LET vTableName =  'POS_Details_' & '$(vMonth)' ;

  LET vReloadTimeIn = Now();

     [$(vTableName)]:

     LOAD  *,

   GuestCheckLineItemID&'|'&SiteID&'|'&POSSystemID as %SiteGuestCheckLineItemID; 

    //Creating a composite key in preceding load for key

  LOAD

     GuestCheckLineItemID,

     LocationID,

     ModifiedBy,

    ModifiedDatetime,

     POSSystemID,

    ReportLineCount,

     ReportLineTotal,

     SiteID,

     Status,

     Tax1Total,

  SQL SELECT *

  FROM "POS".dbo."vw_POS"

WHERE cast(datepart(yyyy,businessDate) as varchar)+cast(datepart(m,businessDate) as varchar)  = '$(vMonth)';

  STORE  [$(vTableName)] INTO [$(vQVDDataDir)\$(vTableName).qvd] (qvd); 

  DROP Table [$(vTableName)];

NEXT

Best Regards,

Ganesh.

[ganeshreddy]

Can anyone help me on with in-between logic instead of comparing with variable in that where clause.

Best Regards,

Ganesh.

[Kushal_Chawda]

Code:

/*********** Getting Distict Year and Month combinations from temporary calendar  ************/

CALENDAR:

  LOAD

  Date($(vMinDate) + RowNo() - 1) AS tmp_Date

  AUTOGENERATE

  $(vMaxDate) - $(vMinDate) +1;

YEAR_MONTH_CALENDAR:              

  LOAD

  Distinct Year(tmp_Date)&num(Month(tmp_Date)) as MonthName

  RESIDENT CALENDAR

;

DROP TABLE CALENDAR;

Let vCount=NoOfRows('YEAR_MONTH_CALENDAR');

/**************  Loop through every month which are generated in YEAR_MONTH_CALENDAR to create monthly QVDs****************/

  FOR i=0 to $(vCount)     

  LET vMonth = Peek('MonthName',$(i),'YEAR_MONTH_CALENDAR');

  LET vTableName =  'POS_Details_' & '$(vMonth)' ;

  LET vReloadTimeIn = Now();

  if isnull(FileSize('$(vQVDDataDir)\$(vTableName).qvd')) then   

     [$(vTableName)]:

     LOAD  *,

   GuestCheckLineItemID&'|'&SiteID&'|'&POSSystemID as %SiteGuestCheckLineItemID;

    //Creating a composite key in preceding load for key

  LOAD

     GuestCheckLineItemID,

     LocationID,

     ModifiedBy,

    ModifiedDatetime,

     POSSystemID,

    ReportLineCount,

     ReportLineTotal,

     SiteID,

     Status,

     Tax1Total,

  SQL SELECT *

  FROM "POS".dbo."vw_POS"

WHERE cast(datepart(yyyy,businessDate) as varchar)+cast(datepart(m,businessDate) as varchar)  = '$(vMonth)';

  STORE  [$(vTableName)] INTO [$(vQVDDataDir)\$(vTableName).qvd] (qvd);

  DROP Table [$(vTableName)];

ENDIF

NEXT

[marcus_sommer]

Faster would be to load the whole sql-table into qlikview (if they is to large you need an incremental load approach), storing it as qvd and apply the where-clause then in a further load in qlikview. This could be an optimized load if you used a where exists() clause - for this you need for each loop an additionally load which creates those field for exists-checking, in your case it will be a date-field.

For both topics - incremental and exists - you will find here various examples:

Advanced topics for creating a qlik datamodel

- Marcus

[ganeshreddy]

Hi Kush,

Thanks for the reply. Your suggested code will work with the incremental load, which i am going to implement later. Right now i am working with the Historical load (imagine there are no QVDs). My actual problem here is with the where clause logic which i have highlighted in the main post, here loading time is very high because of that where clause is hitting entire data for every loop. I am asking for the alternate solution with In-between logic for example if data lies in between 01/01/2013 to 31/01/2013 fetch only that data and store it then fetch feb 2013 data without hitting jan 2013 data and so on . Please help/ suggest me one this.

Best Regards,

Ganesh.

[ganeshreddy · Accepted solution]

Hi All,

Instead of using WHERE cast(datepart(yyyy,businessDate) as varchar)+cast(datepart(m,businessDate) as varchar)  = '$(vMonth)'; in where clause, I have used Where businessDate BETWEEN '$(vMinMonth)' AND '$(vMaxMonth)';  by looping through vMinMonth and vMaxMonth. Code is working fine and i have observed drastical improvement in performance. Thank you all for your support.

Best Regards,

Ganesh.