Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data Modeling Issue

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

snap.pngwhich give me correct total that is 130

but when I used apply map output table is

Applymap.pngwhich 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

join.pngwhich give 160 which is not correct.

Please anyone has any suggestion please share here

please find sample application and data file in attachment

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

Look into the attachment. Hope this will work for you

Regards

View solution in original post

11 Replies
Not applicable
Author

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

Not applicable
Author

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
Specialist III
Specialist III

Use

aggr(Sum(Sales),State,Emp)

as an expression to get the correct value

thanks,

Rajesh Vaswani

Not applicable
Author

it doesnt work..

Anonymous
Not applicable
Author

Hi

In the script

instead of  Inner join use the Left Join

Anonymous
Not applicable
Author

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

Not applicable
Author

Hi,

Look into the attachment. Hope this will work for you

Regards

Anonymous
Not applicable
Author

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:

JoinGranularity.png

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:

ApplymapGranularity.png

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
Not applicable
Author

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.