topic Re: Data Modeling Issue in QlikView

Data Modeling Issue

Tue, 23 Dec 2014 08:42:34 GMT

Hi Friends,

I got an issue while loading data,I have two tables,where I have to merge the tables,when I just load those tables I got following table

which give me correct total that is 130

but when I used apply map output table is

which give correct total i.e. 130 but EMP P is missing because of many to one relationship.

and when I used join the output table is like

which give 160 which is not correct.

Please anyone has any suggestion please share here

please find sample application and data file in attachment

Re: Data Modeling Issue

Tue, 23 Dec 2014 10:27:23 GMT

As per your data , value are repeating for State UP one is showing for P EMP and one is for U EMP.

So in QV join is working properly . But I think your raw data should be EMP wise sale in excel .So when you will calculate sale of any state it give you correct answer.

Or else as per you data you should have any other information to divide the sales respective EMP.

Regards

Re: Data Modeling Issue

Tue, 23 Dec 2014 12:22:26 GMT

yes this is the issue,I want sum of sales for P and for U is 30 but Total sales must be 130,and there should be single table in the data model

Re: Data Modeling Issue

rajeshvaswani77 — Tue, 23 Dec 2014 12:25:04 GMT

Use

aggr(Sum(Sales),State,Emp)

as an expression to get the correct value

thanks,

Rajesh Vaswani

Re: Data Modeling Issue

Tue, 23 Dec 2014 12:31:36 GMT

it doesnt work..

Re: Data Modeling Issue

Anonymous — Tue, 23 Dec 2014 12:34:37 GMT

In the script

instead of Inner join use the Left Join

Re: Data Modeling Issue

Anonymous — Tue, 23 Dec 2014 13:07:21 GMT

Hi Bhawna,

Are P EMP and U EMP having duplicate values?,Maybe use distinct key word in expression

Does your table's have Date field's?,if so what is the format for fields.

Regards

Neetha

Re: Data Modeling Issue

Tue, 23 Dec 2014 13:14:51 GMT

Hi,

Look into the attachment. Hope this will work for you

Regards

Re: Data Modeling Issue

Anonymous — Tue, 23 Dec 2014 13:54:29 GMT

Table:
LOAD State,
EMP
FROM

(ooxml, embedded labels, table is Sheet2);

left join

LOAD State,
Date,
Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Output for Join:

Table:
Mapping LOAD State,
EMP
FROM

(ooxml, embedded labels, table is Sheet2);

NoConcatenate

LOAD State,
Date,
ApplyMap('Table',State)as EMP,
Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Output for Applymap:

Applymap should be done on Unique field,and state field is not unique,it has duplicate values.

My assumption is as applymap takes only first encountered value into consideration ,so in sheet2 state field UP is having 2 records with two employees.

so it takes first value UP For U Emp encountered and maps to another table and duplicated value UP For P Emp is not taken into consideration

and not mapped to other table,so 130 total is wrong.

Where as join takes all possible values,so 160 total is correct.

Regards

Neetha

Re: Data Modeling Issue

Anonymous — Tue, 23 Dec 2014 14:07:34 GMT

As you don't have any unique field in both tables,all possible combinations of values be made.

so any join you make the data will have total 160.

Re: Data Modeling Issue

Wed, 24 Dec 2014 05:58:16 GMT

thank you all for your valuable comment

Re: Data Modeling Issue

Wed, 24 Dec 2014 05:58:37 GMT

thank you sumita it works..