Data Modeling Issue

[#1]

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

[#2]

Hi

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

[#3]

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

[rajeshvaswani77]

Use

aggr(Sum(Sales),State,Emp)

as an expression to get the correct value

thanks,

Rajesh Vaswani

[#5]

it doesnt work..

[Anonymous]

Hi

In the script

instead of  Inner join use the Left Join

[Anonymous]

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

[#8 · Accepted solution]

Hi,

Look into the attachment. Hope this will work for you

Regards

[Anonymous]

Hi

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

[Anonymous]

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.