Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Started with data from two excel files with two sheets each that I concatenated successfully.. stored each concatenation as two separate tables...the below follows the path I took for ONE excel file and ONE concatenation....
I can perform a BINARY load successfully if I do one Table at a time, but I need data from both tables in my QVD. Here's my path so far - help is greatly appreciated
CAPFactTable:
LOAD*
FROM
(ooxml, embedded labels, table is [Feb 28 CAPs]);
Concatenate(CAPFactTable)
LOAD*
FROM
(ooxml, embedded labels, table is [Mar 28 CAPs]);
I then stored each table as a qvd....
STORE CAPFactTable into O:\Report QV automation\04_QVD_Files\CAPFactTable.qvd(qvd);
EXIT Script;
Which then allowed me to load each table separately....
CAPFactTable:
LOAD*
FROM CAPFactTable.qvd(qvd);
And finally perform a BINARY load of my QVD...
BINARY O:\Report QV automation\03_QVD_Maker\Monthly_Reporting_DMG_Revised.qvw;
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
exit script
My problem is that this table, 'CAPFactTable' only contains data from one source. I also want to upload 'IssueFactTable' that I have created in a parallel fashion in a BINARY upload within the SAME QVD, even though they are from unique excel files.
Does this make sense? The help is greatly appreciated!
I'll admit may be confused by a couple of statements in your question. But I clarify that you can only have one BINARY load in a script. Two is not allowed.
In your final dashboard, why not just load the QVD(s) you created? It's unlimited how may QVDs you can load.
-Rob
I'll admit may be confused by a couple of statements in your question. But I clarify that you can only have one BINARY load in a script. Two is not allowed.
In your final dashboard, why not just load the QVD(s) you created? It's unlimited how may QVDs you can load.
-Rob
Thanks Rob.
The only reason I want to do a BINARY load is because it increase the time of my upload so much.
Is the method you proposed just as good?
Brand new to Qlikview
Loading QVDs is not as fast as Binary load, but it is very close.
-Rob
You could put the QVD generation script for both sets of data into one QVW.
Then binary load just that 1 QVW.
If that for some reason is impossible, then the best you could do is Binary load the biggest one and then QVD load the other.
As Rob pointed out - you can only do one binary load in your script, and it has to be the first statement.
The binary load basically takes everything from one existing .qvw file and imports it into another .qvw file. So now you're 'stuck', as you can not run a second binary load from a second 'source' qvw file into your target .qvw.
So, after the first binary load, you could possibly then continue loading more data by loading additional .qvd data files and merging them into the data model that you've got in your target .qvw file. (This allows you to skip at least one .qvd file load.) Sort of clumsy - as this assumes that you're hitting a specific source QVW file, and then doing additional loads from QVD files.
You might try this:
Create extract qvw files with scripts that pull data from your different excel files or other data sources, and then write those sources to .qvd data files. This is something like what you've done with your two different .qvw files right now, except you can use one single extract script, with different tabs to pull from different data sources, and then writing the extracted data into the binary .qvd data format which can be used in multiple places.
Next, build a "data model" .qvw file, with scripts that load data from 1 to many .qvd data files, and merge the data into your common table. You may be doing additional manipulation here (or in the initial extract scripts) to make sure that the data fields all match.
Once your data model .qvw is done doing all the data loading, merging, and transformations, you could then do a binary load into your final dashboard .qvw file, where you have all of your presentation setup (ie, the user .qvw file).
This tiered approach lets you merge multiple data sources into a common data set, do any transformations or manipulations on the input data, and create a coherent data set on the 'back end', without impacting anyone using the front end user dashboard. Once all the heavy work is done on the backend, you can then do a quick binary load to update your dashboard.
Hope that this helps.
This is really great - I tried something rather similar to this but want to see if it works in production before getting back to you. Will revert once I know it works!