Using the "MonthlyWeek" field in both tables

[Anonymous]

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:

load

City,

Store,

Date,

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

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

Value1

from 1.xlsx;

Concatenate(Data)

Data2

load

City,

Store,

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

Value2

from 2.xlsx;

With my best regards,

Evgeniy

[maxgro]

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

Data:

load

     City, Store, Date,

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

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

Value1

from 1.xlsx;

left join (Data)

load

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

     Value2

from 2.xlsx;

[Anonymous]

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]

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

[maxgro]

Could you post your data (excel)?

[Anonymous]

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:

load

     City, Store, Date,

     Day,

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

Value1

from 1.xlsx;

Concatenate(Data)

load

     City, Store,

     Day,

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

     Value2

from 2.xlsx;

With my best regards,

Evgeniy

[maxgro · Accepted solution]

maybe this?

T2:

LOAD City,

     StoreName,

     Day,

     [Value2(Plan)]

FROM

[2.xls]

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

join (T2)

LOAD Distinct

City,

     StoreName,

////     Date,

////     Day,

     MonthlyWeek

//     [Value1(Fact)]

FROM

[1.xls]

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

T1:

LOAD City,

     StoreName,

     Date,

     Day,

     MonthlyWeek,

     [Value1(Fact)]

FROM

[1.xls]

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

DROP Table X;

[Anonymous]

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:

load T1...

load T2...

Join (T2) load distinct... resident T1;

Join(T1) load * resident T2;

Drop table T2;