Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Creating variables to load previous day/month data

I am currently working on a project that involves approx. 23m records per month that I will have to append to a Master Table that I have already created.  What I am trying to do is set up some variables that will take the daily data from the previous day and create a temporary monthly file and append it to the Master Table to be used for insights into how the current month is trending vs history.  But at month end, delete that temporary file (that is appended to my Master Table) and extract a full month of data to append to Master Table for final month end reporting.  Since I am still in testing phase the below script is my two main tables I would have to do it to both tables.  I don't want to left join the two together. The first table is wSAP_FACT_V, the second is Wsap_Dly_Trans_GL_V.  I join them by the Dly_Trans_Row_Id so that I can bring in additional details. 

 

The question is,  how do I create a variable so that I can bring in the data from each table by day, then at month end by full month, store it, then call the saved QVD into another app for optimizing the data this way I can transform, and create other fields based on the data on the backend so that front end users will not have to create master items on the front end.  Like I said before, 1 month of data is approx. 23m each and I want to make sure that the final app doesn't have any latency issues.

 

Below are the scripts that I am using to create my original QVDs so that I don't have to hit live databases during the Transform process.

 

Any help would be greatly appreciated.

Thank you,

Chris

 

wSAP_FACT_V:

select * from FINMAP_PRD_SECVM.wSAP_FACT_V

WHERE WSAP_POST_MTH='08/01/2020' or WSAP_POST_MTH='09/01/2020';

STORE * FROM wSAP_FACT_V INTO 'LIB://EQUIP/EQUIP_REPORTING_QVD/wSAP_FACT_V.QVD'(QVD);

DROP TABLE wSAP_FACT_V;

 

Wsap_Dly_Trans_Gl_V:

select Dly_Trans_Row_Id,

       Vz2_Segmt_Cd,

       If(IsNull(Vz2_Segmt_Cd), 'MISSING', Vz2_Segmt_Cd) AS DLY_TRANS_GL_SEGMT_CD2,

       PS_MKT_CD,

       SHIP_TO_CD,

       GL_ACCT_NUM,

       ITEM_CD 

      from Finmap_Prd_Allvm.Wsap_Dly_Trans_Gl_V

      WHERE post_dt >='2020-08-01' and post_dt<='2020-09-30'; 

STORE * FROM Wsap_Dly_Trans_Gl_V INTO 'LIB://EQUIP/EQUIP_REPORTING_QVD/Wsap_Dly_Trans_Gl_V.QVD'(QVD);

DROP TABLE Wsap_Dly_Trans_Gl_V;

Labels (1)
0 Replies