Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ganeshreddy
Contributor III

Where clause in Monthly QVD for huge data?

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.

1 Solution

Accepted Solutions
Highlighted
ganeshreddy
Contributor III

Re: Where clause in Monthly QVD for huge data?

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.

5 Replies
ganeshreddy
Contributor III

Re: Where clause in Monthly QVD for huge data?

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

Best Regards,

Ganesh.

Re: Where clause in Monthly QVD for huge data?

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

MVP & Luminary
MVP & Luminary

Re: Where clause in Monthly QVD for huge data?

Advanced topics for creating a qlik datamodel

- Marcus

ganeshreddy
Contributor III

Re: Where clause in Monthly QVD for huge data?

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.

Highlighted
ganeshreddy
Contributor III

Re: Where clause in Monthly QVD for huge data?

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.