Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
Some field values are duplicating when they are added into Qlik.
For example,
I have raw data that looks like so:
Item | Q1 Budget | |
Study Room Program Manager | 6000 | |
Study Room Assistant | 1800 | |
Rent | 0 | |
Utilities | 1200 | |
Generator Expenses | 300 | |
Phonebills & Internet Expenses | 125 | |
Professional Fees - Lawyer & Auditor | 1250 | |
Stationary | 128 | |
Public School Supervisors | 0 | |
Printing | 12000 | |
Revision & Consulting | 0 | |
Marketing & Advertising | 0 | |
Web Fees (Social Media) | 1000 | |
Study Room Furniture (Two Class rooms & accessories) | 0 | |
Equipment (Required for bigger premises) | 0 |
But for some reason, when uploaded to Qlik, the values are duplicated.
Why is that? I know I can solve the issue using distinct i.e. Sum(distinct [Quarter One Budget]). But I am curious as to why this is happening. Please can someone explain this to me.
Thank you in advance,
Alison
Hi Alison,
From you posted above looks like your Category table is not associated with Data table, so displaying Q1 Budget by Item will most probably cause some duplication. Rather than resolving this by using sum( distinct MeasureName) in the chart itself this should be resolve in your script by re-designing your data model.
How is your data connected?
If you could post a sample app that would be really helpful.
Micha
Hi
Could you share your code? Is difficult to understand what's happening without see it.
Regards
Please post your load script.
Hello all,
Thank you for your responses, here is the load script:
[Project Budget]:
CROSSTABLE ([Project Budget.Attribute field],[Project Budget.Data field],4)
LOAD [Item],
[Y1 Budget],
[Q1 Budget],
[Q2 Budget],
[42644],
[42675],
[42705],
[42736],
[42767],
[42795]
FROM [lib://Finance Apps/MMKN.xlsx]
(ooxml, embedded labels, table is [Project Budget]);
[Date]:
LOAD
[Project Budget.Data field] as [Project Budget Amount],
[Item] as [Project Budget Item],
[Y1 Budget] as [Year One Project Budget],
[Q1 Budget] as [Quarter One Budget],
[Q2 Budget] as [Quarter Two Budget],
Date#([Project Budget.Attribute field]) as Date
Resident [Project Budget];
Drop table [Project Budget];
[Category]:
LOAD * INLINE [
Item, Category
Public School Supervisor, Activities
Priniting, Activities
Revision & Consulting, Activities
Marketing & Advertising. Activities
Web Fees (Social Media), Activities
Study Room Furniture (Two Class rooms & accessories), Capital Costs
Equipment(Required for bigger premises), Capital Costs
Study Room Program Manager, Salaries
Study Room Assistance, Salaries
Rent, Overhead Costs
Utilities, Overhead Costs
Generator Expenses, Overhead Costs
Phonebills & Internet Expenses, Overhead Costs
Professional Fees - Lawyer & Auditor, Overhead Costs
Stationary, Overhead Costs
];
Hi
Why Category and Date are not joined in your screenshot? In the small code you share are joined?
Why you have this tables called Data-1, Data-2, etc...?
Regards
I have tables called Data-1 and Data-2 because I wanted to have a master calendar.
Hi Alison,
May be In your Master table have duplicate values and you applied master on transaction data . That may be cause.
Thanks,
Arvind Patil
If you change this line in Date:
[Item] as [Project Budget Item],
And write this
Item
Item will be linked in both tables.