Discussion board where members can get started with Qlik Sense.
I am trying to create a App that will analyse my ebay sales. The challenge is that i dont have all the data in one place. I have the following excel exports.
Ebay: Sales history which includes transaction ID, Sold Price, Ebay Fees, Customer Information, SKU
Shipping software: transaction ID, Customer information, shipping Fees
Paypal: Transaction ID, Paypal Fees, Customer information
Our Database dump excel: product cost, SKU.
I am trying to achieve the following.
Link Ebay sheet to Shipping sheet and Paypal sheet using the transaction ID, this will find and match all the identical transactions, also I want to link our data base shee to Ebay sheet using the SKU, Final step will be as follow
Transaction ID, SKU, Sold Price - Ebay Fee - Paypal Fee - Shipping Fee then take the total and deduct it from the cost.
my main question is can we achieve this in qlik ? If yes can i make the application so that i can import these sheets on daily bases to create daily profit reports ? and HOW can i make this happen, I have tried to play around the application, watched the videos but it is over whelming.
Any help will be highly appreciated.
That shouldn't be a big problem.
The ebay sales can be loaded into a Sales table. The transaction ID field is the logical choice for the key field that will link it to the Shipments table and the Payments table.
Load the shipments data in a Shipments table that's linked with the Sales and Payments table using the transaction ID key.
Load the paypal payments data in a Payments table that's linked with the Sales and Shipments table using the transaction ID key.
All the customer information can be consolidated in a Customers table. Keep only a Customer ID key in the Sales table to link it with the Customers table.
Finally the Products table with the data from your database which is linked to your Sales table using the SKU ID field.
Make sure the key fields that are used to link the tables have the exact same case sensitive name in the tables that should be linked.
so far this is what i have done.
Data Base Sheet
create assassination between
Ebay sheet and PayPal sheet Transaction ID
Ebay Sheet and Paypal Sheet Transaction ID
Ebay Sheet and Shipping Sheet Transaction ID
Ebay Sheet and DataBase SKU
Please tell if so far all is good.
Next i need to find matching information and run the formula to calculate the sold price minus fees and charges from he cost. How can i achieve that section
Please tell if so far all is good.
I'm afraid I have no idea if all is good. I can't see what you've done and what the data model in your Qlikview document now looks like. Perhaps you can create a small qlikview document with some example data. See this document: Preparing examples for Upload - Reduction and Data Scrambling
Hope you are doing well!
Please use below sample script which will join & load data from different sources and calculate the required field. You can modify the script as per your need.
CustomerInformation AS eBayCustomerInformation,
(ooxml, embedded labels, table is eBay);
CustomerInformation AS ShippingCustomerInformation,
(ooxml, embedded labels, table is Shipping);
CustomerInformation AS PaypalCustomerInformation,
(ooxml, embedded labels, table is Paypal);
(ooxml, embedded labels, table is DBDump);
ProductCost - (SoldPrice - eBayFees - PaypalFees - ShippingFees) AS Profit
DROP Table SampleSales;
Hope this will help. !