Loading Multiple Excel Sheets - Usage Control Report

[alagesm]

Hi there Experts,

I have few excel sheet data that need to be used for my reporting. I loaded in Qlik Sense, but the issue I am having is getting the accurate results.

sheet 1 

Order details - User will request material based on usage needed, but the supervisor will authorize based on projection - on allowable qty.

Sheet 2

Order Delivery - Supervisor will review sheet 1 and issued the actual qty or sometimes based on stock balance.

Sheet 3

Actual Usage - user will utilize the inventory received and record how many/much qty been used.

I have created 3 sheet 

1. MVMT_DATA

2.MOU_DATA

3.AC_DATA

All fields has similar data (Date,Code, Name, Qty,etc)

When I load to QS I am able to get the inventory, but when I select the particular date I am not able to get the actual usage. So I have to create 3 date  for each selection category. I don't think this is a proper way to do it, but  I am not sure how else to achieve this.

Any suggestion is much appreciated.

Thank You

AL

[siddharthbanerjee1998 · Accepted solution]

Hi,

I don't think that I've understood the exact nature of the excel sheets as far as the business understanding goes, however I think I can help you out with a couple of things. 

I noticed that in the manner that you have loaded the excel sheets in Qlik Sense, they are being uploaded as 3 separate tables. There is no link or key which connects all three tables. Because of this reason, each table (or excel's) date field will modify entries in the front-end for only that particular table (or excel). 

What you need to do is, use a common column that is available in each excel (such as Item Code) and link the 3 tables in Qlik Sense (either using the data manager or the Data Load Editor). Once that is done, your data model should look like this.

As you can see, the 3 tables are linked using the Item code fields (That I have renamed to be the same).

To solve the date issue,  you can use a master calendar script to generate a calendar as a separate table altogether. 
After which, you can follow the same step as above and ensure that the date field in each table is linked to the master calendar table. 

Then, you can simply use the master calendar date in the front end as a filter and that will automatically modify all entries for you in the front-end. 

Please note that for this to work, all 3 dates need to be the same date column. As in, all of the date fields must indicate similar type of entry. Because if not, then you would not be able to identify each date column separately in the front end.

Please let me know if this helps your query.

[alagesm · Accepted solution]

Hi @siddharthbanerjee1998 ,

Thank You.

All 3 files are linked via data manager, and yes all of them have Item Code as the main key. I think my problem is wanting to use one selector of date compared to 3 now.

I can make it work if its 2 set of file, when comes to 3 it didn't work. It could be like what you said, maybe I am not using master calendar date.  Let me see if this will solve my problem. 

Thank You soo much for your help. 

Thank You

Al

[siddharthbanerjee1998]

Hi @alagesm ,

No problem at all!

If this has solved your problem, please mark the response as a solution so that this thread can be closed.

Regards,

Siddharth.