Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
Please suggest on below issue, have 2 tables 1) Sales Quota File --> Excel Feed 2) Invoice Table.
Joining the tables based on the Key created, but not getting the values from the Sales Quota in output table when that particular ChainID Sales not available in Invoice tables. Details below.
Sales Quota File | |||
Sales Executive | Company Chain ID | Sales Quota | Month |
Suresh | 121 | 100 | 1/1/2023 |
Suresh | 123 | 150 | 2/1/2023 |
Suresh | 124 | 300 | 3/1/2023 |
Ajay | 432 | 200 | 1/1/2023 |
Ajay | 243 | 250 | 2/1/2023 |
Ajay | 342 | 350 | 3/1/2023 |
Preeth | 567 | 100 | 1/1/2023 |
Preeth | 675 | 125 | 2/1/2023 |
Preeth | 665 | 355 | 3/1/2023 |
Ramesh | 876 | 455 | 1/1/2023 |
Ramesh | 768 | 660 | 2/1/2023 |
Ramesh | 776 | 720 | 3/1/2023 |
Invoice Table | |||
Sales Executive | Company Chain ID | Quota Achieved | InvoiceDate |
Suresh | 123 | 200 | 2/1/2023 |
Suresh | 124 | 300 | 3/1/2023 |
Ajay | 432 | 200 | 1/1/2023 |
Ajay | 243 | 250 | 2/1/2023 |
Ajay | 342 | 350 | 3/1/2023 |
Preeth | 567 | 100 | 1/1/2023 |
Preeth | 675 | 125 | 2/1/2023 |
Preeth | 665 | 355 | 3/1/2023 |
Ramesh | 876 | 455 | 1/1/2023 |
Ramesh | 768 | 660 | 2/1/2023 |
Ramesh | 776 | 720 | 3/1/2023 |
Output table | ||||
Sales Executive | Company Chain ID | Sales Quota | Quota Achieved | Month |
Suresh | 121 | 100 | Null | 1/1/2023 |
Suresh | 123 | 150 | 200 | 2/1/2023 |
Suresh | 124 | 300 | 300 | 3/1/2023 |
Ajay | 432 | 200 | 200 | 1/1/2023 |
Ajay | 243 | 250 | 250 | 2/1/2023 |
Ajay | 342 | 350 | 350 | 3/1/2023 |
Preeth | 567 | 100 | 100 | 1/1/2023 |
Preeth | 675 | 125 | 125 | 2/1/2023 |
Preeth | 665 | 355 | 355 | 3/1/2023 |
Ramesh | 876 | 455 | 455 | 1/1/2023 |
Ramesh | 768 | 660 | 660 | 2/1/2023 |
Ramesh | 776 | 720 | 720 | 3/1/2023 |
Sales Quota File | Key: SalesExecutive+CompanyCHainID+Month |
Invoice Table | Key: SalesExecutive+CompanyChainID+InvoiceDate |
Data model is star schema where Invoice table is fact table and remaining are dimension table and Invoice table is linked to mastercalender based on invoice date field.
Thanks in Advance.
I think I would consider both tables as facts and just concatenating them - by harmonizing the field-names, like:
facts:
load Executive, ID, 'Sales Quota' as KPI, [Sales Quota] as Value, Month as Date
from Sales;
concatenate(facts)
load Executive, ID, 'Sales Achieved' as KPI, [Quota Achieved] as Value, InvoiceDate as Date
from Invoice;
means no trouble in creating the keys and the risks of loosing/duplicating records/information by any join-approaches and an easy access to the needed data by using the KPI as dimension and/or selection and/or set analysis condition.
@marcus_sommer Thanks for response, but here invoice table is having more column other than what i mentioned, i have highlighted the columns which is using to create key
That a fact-table is combined per concatenate from multiple sources and becomes on this way more or less asynchronous is neither technically nor logically an issue.
Beside of this there may be further data-parts which might be transformed, for example more measure-fields which are transferred in such a KPI and Value structure instead of being kept in a crosstable-logic as well as outsourcing some fact-fields into further dimension-tables and many more possibilities ...
It will depend on various factors which measures are the most suitable ones. In this regard it's also useful to look on the size of the data-sets. If for example the invoices have 95% of the records and the sales about 5% it might be not worth to optimize the structures and logic even if this would be possible.
Tried using the concatenation w.r.t 4 fields, but its making issue in values for the other dimension.
seems its acting as cross table and hence getting more number.
Excuse for delayed response... @marcus_sommer
@sunny_talwar.. can you pls suggest on this thread
By concatenating several fact-tables into a single-fact-table it should work like above described. Therefore I assume that there are more fact-tables and/or any invalid associations and/or synthetic keys and/or circular loop between the tables.
Try to keep it so simple as star-scheme with one fact-table and n dimension-tables. Of course there are also other ways - but all of them will be much more complex ...