Joining dates

[Anonymous]

Good day!

Colleagues, i have some simple model to understand how to join (fill) values by some key field.

I load main table from sheet S1:

Id	Date	Sales
1	01.01.2016	34
1	04.01.2016	7
1	06.01.2016	2
1	07.01.2016	5

then i need to join to this table all dates by Id field from sheet S2.

My necessary result table:

Id	Date	Sales
1	01.01.2016	34
1	02.01.2016	-
1	03.01.2016	-
1	04.01.2016	7
1	06.01.2016	2
1	07.01.2016	5
1	10.01.2016	-

Help, please!

[Chanty4u]

T1:

load *,

All fields

from Table 1:

concatenate(T1)

T2:

Load

*,

all

from

sheet 2;

[sunny_talwar]

May be you want to concatenate the two tables?

[kkkumar82]

If both the sheets contain same number of fields with same names they will be automatically concatinated I mean joined in your language.

Is that what you wanted.

[sunny_talwar]

Why did you remove your 1st comment? I think Master Calendar is the way to go here Kumar

[kkkumar82]

Hi Sunny,

Love to talk to you once directly if possible, over enthusiast .

I thought by looking the post that there is only one table with the first dates and need to fill the other dates, so went for suggesting master calendar, but as I re looked there are two tables that is why I removed the comment.

I thing I am correct atleast now.

[Anonymous]

i don't know what in this case i must to do (Join or Concat)..

I need to get result described above..

This is test example, but in my real data there more fields (id, City and others), but dates must be joined to Main tables by Id field.

[Anonymous]

Thanks.

But i thing, tables concatenating  is not right solution for my problem.

For example, my first table can consist one more field, for example Warehouse id (Value 1 in field Id has matching value 3334 in Warehouse_Id field ) and in this case result table after joining dates must be:

Id	Warehouse_Id	Date	Sales
1	3334	01.01.2016	34
1	3334	02.01.2016	-
1	3334	03.01.2016	-
1	3334	04.01.2016	7
1	3334	06.01.2016	2
1	3334	07.01.2016	5
1	3334	10.01.2016	-

Thanks for help.

[jlongoria]

First, please review this article on join, keep, and concatenate: Understanding Join, Keep and Concatenate

One approach given the spreadsheet data and your expected results is to outer join S1 and S2.

MyData:
LOAD Id,
Date,
Sales
FROM
[data.xlsx]
(ooxml, embedded labels, table is S1);

Outer Join (MyData)

LOAD Id,
Date
FROM
[data.xlsx]
(ooxml, embedded labels, table is S2);

[Anonymous]

Thanks Dear.

In your example.

If i add some one field to S1 sheet, for examplem warehouse, Dates from S2 don't connect with it:

MyData:

LOAD Id,

Warehouse,

Date,

Sales

FROM

[Test.xlsx]

(ooxml, embedded labels, table is S1);

Outer Join (MyData)

LOAD Id,

Date

FROM

[Test.xlsx]

(ooxml, embedded labels, table is S2);

Result:

Id	Warehouse	Date	Sales
1	Main	01-01-2016	34
1	Main	04-01-2016	7
1	Main	06-01-2016	2
1	Main	07-01-2016	5
1		02-01-2016
1		03-01-2016
1		10-01-2016

Necessary result:

Id	Warehouse	Date	Sales
1	Main	01-01-2016	34
1	Main	04-01-2016	7
1	Main	06-01-2016	2
1	Main	07-01-2016	5
1	Main	02-01-2016
1	Main	03-01-2016
1	Main	10-01-2016