Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

Using the "MonthlyWeek" field in both tables

Hello!

Description:

There are 2 tables. The 2nd table contains values which are repeatable for each week (doesn't contain a "MonthlyWeek" field).

Question / Problem:

How to add the "MonthlyWeek" field to the 2nd table in a better way?! (fields from both tables will be used in one view)

- I've tried to add subfield( '1;2;3;4;5' , ';') as MonthlyWeek to the second one but I don't think that it's a good solution.

Data:

City,

Store,

Date,

Day, // Mon, Tue, Wed ...

Ceil(Day(Date)/7) as  MonthlyWeek,  // 1, 2, 3...

Value1

from 1.xlsx;

Concatenate(Data)

Data2

City,

Store,

Day, // Mon, Tue, Wed ...

Value2

from 2.xlsx;

With my best regards,

Evgeniy

1 Solution

Accepted Solutions
MVP

maybe this?

T2:

StoreName,

Day,

[Value2(Plan)]

FROM

[2.xls]

(biff, embedded labels, table is Sheet1\$);

join (T2)

City,

StoreName,

////     Date,

////     Day,

MonthlyWeek

//     [Value1(Fact)]

FROM

[1.xls]

(biff, embedded labels, table is Sheet1\$);

T1:

StoreName,

Date,

Day,

MonthlyWeek,

[Value1(Fact)]

FROM

[1.xls]

(biff, embedded labels, table is Sheet1\$);

DROP Table X;

7 Replies
MVP

Do you mean you have the same Value2 for every City, Sotre, Day (and no duplicates for these 3 fields)?

Data:

City, Store, Date,

Day, // Mon, Tue, Wed ...

Ceil(Day(Date)/7) as  MonthlyWeek,  // 1, 2, 3...

Value1

from 1.xlsx;

left join (Data)

City, Store, Day, // Mon, Tue, Wed ...

Value2

from 2.xlsx;

Anonymous
Not applicable
Author

Hi Massimo Grossi,

Not quite. The 2nd table contains the data like:

Store, Day, Value2

Store1, Mon,  0

Store1, Thu,  1

Store1, Wed,  0

... (7 weeks day)

Store2, Mon,  1

Store2, Thu,  0

Store2, Wed,  1

...(7 weeks day)

These values are the same for each number of monthly week.

Thanks for the quick response!

Anonymous
Not applicable
Author

It would be better to say that:

if I use "left join" I won't see the missing (hidden) data

if I use "Concatenate" I will see the full picture but a lot of additional rows will be added

What should I do? Are there just two option in this case?

With my best regards,

Evgeniy

MVP

Could you post your data (excel)?

Anonymous
Not applicable
Author

Hi Massimo Grossi,

I've attached just sample files based on the existing data.

Hope, It will be enough to understand the idea.

Description:

Value1 = Fact flag

Value2 = Plan flag

I will compare Value1 with Value2 in one view by MonthlyWeek and Day

I think, I need to look at "concatenate" and left like this:

Data:

City, Store, Date,

Day,

Ceil(Day(Date)/7) as  MonthlyWeek,

Value1

from 1.xlsx;

Concatenate(Data)

City, Store,

Day,

subfield( '1;2;3;4;5' , ';') as MonthlyWeek,

Value2

from 2.xlsx;

With my best regards,

Evgeniy

MVP

maybe this?

T2:

StoreName,

Day,

[Value2(Plan)]

FROM

[2.xls]

(biff, embedded labels, table is Sheet1\$);

join (T2)

City,

StoreName,

////     Date,

////     Day,

MonthlyWeek

//     [Value1(Fact)]

FROM

[1.xls]

(biff, embedded labels, table is Sheet1\$);

T1:

StoreName,

Date,

Day,

MonthlyWeek,

[Value1(Fact)]

FROM

[1.xls]

(biff, embedded labels, table is Sheet1\$);

DROP Table X;

Anonymous
Not applicable
Author

A very good solution! Thank you for finding the time to help me!

It works fine based on this sample data, but the real data tables are different in some fields so it has also added lots of rows

I've tried based on your suggestion: