topic Re: Qlik Duplicating Values in App Development

Qlik Duplicating Values

Fri, 21 Jul 2017 15:20:05 GMT

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

Re: Qlik Duplicating Values

kuczynska — Fri, 21 Jul 2017 15:56:41 GMT

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

Re: Qlik Duplicating Values

jmvilaplanap — Fri, 21 Jul 2017 16:03:01 GMT

Could you share your code? Is difficult to understand what's happening without see it.

Regards

Re: Qlik Duplicating Values

passionate — Fri, 21 Jul 2017 17:02:31 GMT

Please post your load script.

Re: Qlik Duplicating Values

Mon, 24 Jul 2017 08:25:34 GMT

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

];

Re: Qlik Duplicating Values

jmvilaplanap — Mon, 24 Jul 2017 09:00:07 GMT

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

Re: Qlik Duplicating Values

Mon, 24 Jul 2017 09:06:23 GMT

I have tables called Data-1 and Data-2 because I wanted to have a master calendar.

Re: Qlik Duplicating Values

arvind_patil — Mon, 24 Jul 2017 09:16:59 GMT

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

Re: Qlik Duplicating Values

jmvilaplanap — Mon, 24 Jul 2017 09:51:59 GMT

If you change this line in Date:

[Item] as [Project Budget Item],

And write this

Item

Item will be linked in both tables.