add extra data from excel on existing APP in Qlik Sense

[dreweezy]

I have uploaded a similar question on a different forum but I believe this is the correct place for my question. I currently have some apps built in Qlik Sense through an Oracle SQL database. I will now have additional data coming in as an excel file that needs to be integrated together with my already connected data from the sql database. The excel spreadsheet does have an associated field/column ("YEAR") that also includes many new columns that are not currently existing in my database. For an example -->

SQL Database data (currently in my Qlik Sense APPS)

New data to integrate coming from excel spreadsheet. You can see I have 2 new fields (total pay and total bonus) that are no existing in my sql database.

I've tried very basic methods (one from Mike Tarralo) where I would just throw my excel spreadsheet into a chart and it will take me to my data load manager screen, but after about a 20 min wait time this task has failed. Mind you, the above data is just a sample and very simplistic compared to what I currently have. But that is the whole idea. I'd like to add new data from a different source (excel) to my already existing connected data (sql database) and matching them on my matching field - "YEAR". The end result should add the new fields that are not existing in my sql database and add those new fields once I throw in this excel spreadsheet. If the only way to do this is through the data load editor, can someone guide me on how to properly load this and matching it on the YEAR column?

*I did conduct a sample and checked out how the data load editor generates the script for me when I do add in a small set of test data.

Unqualify *;

[Sheet1]:
LOAD
[YEAR],
[totalPay],
[totalBonus],
FROM [lib://Desktop/additional data test.xlsx]
(ooxml, embedded labels, table is Sheet1);

My guess is that it is not this simple to just add this similar syntax in my script to have this read the data correctly. I am not able to give this a shot since it takes really long (20+ minutes) to even see an output and wanted to ask before making any final changes to the script.

Any help would be appreciated. Thank you.

[crusader_]

Hi,

If you 100% sure that the linkage needs to be done only by Year field, then it's pretty much as simple as you've shown on your example.

For testing purposes try to limit your load with FIRST <X> keyword like below:

Fact_from_Oracle:
FIRST 100
LOAD
Field1,
Field2,
...
FieldN;
SQL SELECT

Field1,
Field2,
...
Year
FROM owner.db.TABLE/VIEW;
....
Data_From_Excel:
FIRST 100 LOAD
Field001,
Field002,
...
Year
From [<Path_to_Your_Excel_file.xlsx]
(ooxml, embedded labels, table is Sheet1);

Hope this help.

//Andrei

[dreweezy]

Thank you for your input. I got it to work. But for some reason, when I brought in the excel into the load editor, the "YEAR" column wouldn't work because it was not unique? I changed the Label to "YEAR1" so that the script will successfully process. Is there a way where I can consolidate my YEAR and YEAR1 columns? I only did that because the editor forced me to create a unique field name. Also, the *FIRST* function you mentioned in the script. It looks like it'll shorten the duration for loading the data, but does it only load the first 100 rows or just reads the first 100 rows? Thanks again.

[crusader_]

Hi,

"First" just fetches first X number of rows, which is as I said just for testing purposes (not to wait 20 minutes to load). In case with SQL query in above example FULL query will be executed on DB side and from the resulting set only first 100 rows will be loaded to Qlik.

If you get error that "column name must be unique" - that means that your single load statement contains non-unique columns, and there is nothing to do with the rest data model.

What means "consolidate"? Would you like to join or concatenate (e.g. union) tables? Or you want to load new table separately?

Could you please paste(attach) a script, so I could see what's happening there?

//Andrei

[dreweezy]

Crusador - thank you. Here is my script below.

-----------------------------------

Unqualify *;

LIB CONNECT TO [Oracle_10.205.142.72];

[BBO_POB_1_VW]:
LOAD
 [LEDGER_ACTIVITY],
 [LEDGER_LINE_ACTIVITY],
 [CLIENT_ID]
 [BBO_POB_1] AS [KLA_POB_2-BBO_POB_1],
 [POA_ID],
 [BILL_TYPE_ID],
 [STATUS_ID],
 [COST_QTY],
 [COST_AVAILABLE],
 [COST_GOODS],
 [TOTAL_COST],
 [NET_COST],
 [COST_UNAVAIL];
SELECT ......
FROM .......;

[KLA_POB_2_VW]:
LOAD
 [KLA_POB_2] AS [KLA_POB_2-BBO_POB_1],
 [YEAR],
 [ACCOUNT_TITLE],
 [ACCOUNT_NUMBER],
 [ACCOUNT_DAILY],
 [ACCOUNT_ORDER],
 [CLIENT_ORDER],
 [CLIENT_NAME],
 [CLIENT_CODE],
 [DEPT_NO],
 [DEPT_CODE],
 [DEPT_TITLE],
SELECT ......
FROM .........;

[Sheet1]:
LOAD
 [TITLE],
 [CLIENT_ID]
 [NEW_AMOUNT]
 [OLD__AMOUNT]
 [APPROVED_DATE],
 [DENIED_DATE],
 [COMPLETE_DATE],
 [TOTAL_DAYS],
 FROM [lib://Desktop/TESTING_DATA_IMPORT.xlsx]
(ooxml, embedded labels, table is Sheet1);

As you can see - my "SHEET1" at the bottom of the script has some new fields and should be mapping by using the [CLIENT_ID] field as this field is in both my first table/view and excel. But when I try to load data, it tells me it needs to be unique, but it is already unique and I would like my CLIENT_ID from excel to map to the [BBO_POB_1_VW] table/view.

[crusader_]

Logically script looks fine, with exception of number of syntax errors... (red bold Arial Black in below script)  Check if this the case, just fix them if not could you please paste full script (attach as a txt file) without modifications?

Also would be great to get a screenshot of error message.

Unqualify *;

LIB CONNECT TO [Oracle_10.205.142.72];

[BBO_POB_1_VW]:
LOAD
 [LEDGER_ACTIVITY],
 [LEDGER_LINE_ACTIVITY],
 [CLIENT_ID],

 [BBO_POB_1] AS [KLA_POB_2-BBO_POB_1],
 [POA_ID],
 [BILL_TYPE_ID],
 [STATUS_ID],
 [COST_QTY],
 [COST_AVAILABLE],
 [COST_GOODS],
 [TOTAL_COST],
 [NET_COST],
 [COST_UNAVAIL];
SELECT ......
FROM .......;

[KLA_POB_2_VW]:
LOAD
 [KLA_POB_2] AS [KLA_POB_2-BBO_POB_1],
 [YEAR],
 [ACCOUNT_TITLE],
 [ACCOUNT_NUMBER],
 [ACCOUNT_DAILY],
 [ACCOUNT_ORDER],
 [CLIENT_ORDER],
 [CLIENT_NAME],
 [CLIENT_CODE],
 [DEPT_NO],
 [DEPT_CODE],
 [DEPT_TITLE];
SELECT ......
FROM .........;

[Sheet1]:
LOAD
 [TITLE],
 [CLIENT_ID],
 [NEW_AMOUNT],
 [OLD__AMOUNT],
 [APPROVED_DATE],
 [DENIED_DATE],
 [COMPLETE_DATE],
 [TOTAL_DAYS],
 FROM [lib://Desktop/TESTING_DATA_IMPORT.xlsx]
(ooxml, embedded labels, table is Sheet1);

Hope this helps

//Andrei

[dreweezy]

Thanks again for your help. What I did instead now was just separate out my CLIENT_ID column in my [BBO_POB_1_VW] and CLIENT_ID in my excel spreadsheet. So now it looks like this.

FROM ORACLE*

[BBO_POB_1_VW]

CLIENT_ID

FROM EXCEL * 

SHEET1

CLIENT_ID2

 did a simple KPI for both these fields when separated and it gives me the right count (300 for SQL and 100 for excel) whereas if I link these together in the script it rolls the number up to 400 which is incorrect. It should still be 300. Now I think I need to do some kind of left join in order to get all the new columns from excel that are not in my sql database that matches with the CLIENT_IDs. I have laid it out to show you a quick example.

SQL import

Excel import

What I am trying to achieve in QLIK - 

Some of the CLIENT_ID wont necessarily match up but the whole idea is to bring the excel sheet with existing CLIENT_ID with new columns that does not currently reside in my sql database. 

[crusader_ · Accepted solution]

Ok, try below:

Table1:
*****
FROM Oracle....;

left join(Table1)
Load
****
From Excel;

Assuming your have common field (Client_ID) in your case...

Hope this helps.

//Andrei

[dreweezy]

I did a left join and the CLIENT_IDs are now matching up and not double counting after the left join. New problem is now my SALES column is a way higher amount than what it should be.. any thoughts on this? I tried to see where the discrepancy is coming from but not much luck.

[dreweezy]

Figured it out lol.. was doing an accidental sum on the sales field. Thanks for your help! Much appreciated and helping me find a solution!