If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I have two table which are QVD load InvoiceXtable(20M Rows) & CallData(6M Rows) are two big tables in the datalayer.
I want to tune the above two table. what is the best solution for tuning the table..
For reference, please find the attached screenshot
There is not much one can do re tuning QVD files.
Except maybe partition them into seperate monthly QVD's, but could well mess up any incremental loads.
Uses Exists and not Exists condition or Group by aggregation function to reduce the Rows and size.
You can make sure that the QVD doesn't contain fields that you are not using. Also, and date/time fields where you don't need the time you can make them just dates and drop the times.
Other than that there is not a lot you can do on the front end to make them smaller.
Also, those sizes are not really what I would consider large.
count(distinct IF(InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit), CallerNumber)) / count(distinct CallerNumber)
Is the above expression gives bad performance? If yes, please suggest how we can modify above expression?
I assume InvoiceDateDT and CallDateDT are in different tables in your data model and doing calculations across a join between two tables is resource hungry and hence slow.
Could you post a screenshot of your data model ?
Also what is in your variable vConversionLimit ?
Better would be to put the condition on the outside of the aggregation and not the inside. I mean:
IF(InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit),
count(distinct CallerNumber), 0) / count(distinct CallerNumber)
If Marcus's suggestion does not give you enough improvement then think about concatenating your tables InvoiceXTable and CallData together, thus creating a classic star schema with a single central fact table.
You will need to think carefully before doing this, as there may be implications dependent on what your front expressions are.