topic Re: Duplicated field values problem in QlikView

Duplicated field values problem

simongoodman — Mon, 19 Mar 2012 11:44:35 GMT

I have a model that has some customers who can be using several services that sit in one or more reporting sectors. My problem is this qvw gives the same total fee in each sector category and not the actual fees per sector.

I have looked at the Table Viewer, the column names in the various tables and the synthetic keys. I have tried to remove the synthetic keys but ended up making it worse.

Has anyone out there seen this problem and have advice or a solution I can try?

Duplicated field values problem

Mon, 19 Mar 2012 18:07:01 GMT

Can you maybe attach the qvw or a sample of the code that is creating problems so we can better assist you?

Re: Duplicated field values problem

simongoodman — Tue, 20 Mar 2012 08:27:04 GMT

Attached is a word doc with the code, an object example of the problem and

the table viewer. I appreciate any help as I have only recently started

workng with Qlikview. Can you tell me why this prolem has occurred?

On Mon, Mar 19, 2012 at 6:07 PM, bapperson <

Duplicated field values problem

jvitantonio — Tue, 20 Mar 2012 08:36:57 GMT

Those synth keys are there becuase you are joining the tables by more than 1 key. If you want to avoid them you need to join the tables only using 1 key.

Duplicated field values problem

Tue, 20 Mar 2012 17:26:58 GMT

I will try to change this to my best guess of what you are shooting for.

It seems that Heirarchy Name is something you actually want to join the tables on, so we will leave that alone. See the changes below.

Directory;

LOAD

[Hierarchy Name],

[Week No],

Date,

[Pay Cycle],

[Target Payslips],

[Target Net Fee],

[Base Client]

FROM

(ooxml, embedded labels, table is Sheet1);

Directory;

LOAD Date,

[Financial Year],

Quarter,

[Accounting Month],

[Accounting / Payroll Week] as [Accounting / Payroll Week cal],

WeekDay,

[Year Ref] as [Year Ref cal],

Month,

MonthYear,

Day

FROM

(ooxml, embedded labels, table is calender);

Directory;

LOAD [Client Name],

[New Client Name],

[Hierarchy Name],

Sector,

[Reporting Sector],

[Account Manager]

FROM

(ooxml, embedded labels, table is [ClientHierarchy]);

Directory;

LOAD [Hierarchy Name],

Date as [Date Sheet2],

[Pay Cycle] as [Pay Cycle Sheet2],

[Gross Fees],

[Total Rebate],

[Net Fee],

[Actual Payslips],

[Year Ref] as [Year Ref Sheet2],

[Accounting / Payroll Week] as [Accounting / Payroll Week Sheet2],

[2011],

[2012]

FROM

(ooxml, embedded labels, table is Sheet2);

As I am unsure what you are actually trying to join the tables on this is just a guess, but it will remove synthetic keys (I hope). The basic idea is that you should make sure anything you do not wish the tables to be joined on should be renamed.

So when you see $Syn3 and below it you see:

Date

Accounti...

year Ref

Heirarch

Pay Cycle

Then you need to rename most of those fields to make sure that they are not the same in both tables.

Unless of course you are intending to join the tables on multiple keys.

Hope this clears things up a bit!

Re: Duplicated field values problem

simongoodman — Wed, 21 Mar 2012 16:20:30 GMT

Thanks alot for the helpful reply. I had tried to rename fields once before

but that seemed seemed to lose interconnectedness from those fields. I also

thought to use autonumbers as a way of creating the equivalent of ID number

specific fields to reduce multiple occurences of names in the tables.

Should I try Joins after renaming these fields and if so what type would

you suggest? An example would be great.

On Tue, Mar 20, 2012 at 5:27 PM, bapperson <

Duplicated field values problem

Wed, 21 Mar 2012 17:39:47 GMT

You should leave the field names the same if the data occurring in both places is the same. For example if the Date and Heirarchy Name coming from sheet2 is the same date and Heirarchy Name coming from Sheet1 then you should no alias them.

One somewhat easy way to join the tables without having to worry about which type of join is just to name different tables in your script. QlikView will then link things automatically.

For example you could do:

Sheet1:

LOAD

[Hierarchy Name],

[Week No],

Date,

[Pay Cycle],

[Target Payslips],

[Target Net Fee],

[Base Client]

FROM

(ooxml, embedded labels, table is Sheet1);

Calender:

LOAD Date,

[Financial Year],

Quarter,

[Accounting Month],

[Accounting / Payroll Week],

WeekDay,

[Year Ref],

Month,

MonthYear,

Day

FROM

(ooxml, embedded labels, table is calender);

etc...

This will find the common field of Date between the two tables and link them automatically.

Otherwise If you have a main table where you know there is always an entry I would start with that and if you have other tables where there might not always be a match use a left join as it will do everything in the first table that is in the second. Or if you already know there is a matching record for everything in every table use inner join to find exact matches between tables.

An example would be:

Table1:

Table2:

ID, num

3, 100

4, 200

If you did Table1 Left Join Table2 you would get:

ID, num

1, -

2, -

3, 100

4, 200

- is null or blank

Whereas Table1 inner join Table2 would get:

ID, num

3, 100

4, 100

Re: Duplicated field values problem

simongoodman — Fri, 23 Mar 2012 14:07:10 GMT

Many thanks for the join examples, they make sense. I will try and

experiment with them.

I understand that with Date and Hierarchy Name in sheet 2 and sheet 1

they can be left alone so Qlikview automatically links them. The fact

they are also in Target meant a synth key was created so I will work

on that.

Interestingly the other synth keys were created although there were

only two occurrences of the same name in two different fields. I

believe Qlikview created the synth keys because it's logic said there

was a loop. Something I failed to see when I was designing the table

layout and still is not so obvious to me. This gives me something to

think about.

Once again thanks for your help, much appreciated.