Join with no concatenation

[Anonymous]

I have two tables that I would like to keep in its orginial state but I need to create an if statement using columns from both tables.

I'm reporting off both tables but when I create my if statement I have to merge the tables. Anyway around keeping both tables as is and creating my if statement?  The below syntax creates one table, "Indicator" and expands my results on both sides. I hope I'm clear in my ask...

StartDate:
LOAD *
FROM
ExampleData.xlsx
(ooxml, embedded labels, table is StartDateData);

EndDate:
LOAD *
FROM
ExampleData.xlsx
(ooxml, embedded labels, table is EndDateData);

JOIN (EndDate)
LOAD *
Resident StartDate ;
DROP TABLE StartDate;

Indicator:
LOAD
IF(EndDATE - StartDate <= 15, 'Y', 'N') AS Gap_Indicator,
ID,  StartDate, EndDATE
RESIDENT EndDate;
DROP TABLE EndDate

[MindaugasBacius]

I suggest you to join EndDate field to StartDate table and to join StartDate field to EndDate table.

Then calculate Gap_Indicator in both tables.

Then Drop field EndDate from StartDate and drop field StartDate from EndDate table.

[vinieme12]

Can you attach your ExampelData file?

You can join your tables by a KEY field, which is ID in your sample here

[Anonymous]

Attached.

[sunny_talwar]

Which of the tables will reside the new indicator field? or is that going to be a third table?

[Anonymous]

I think I can just create a duplicate table, change column names and utilize. Something like below,

StartDate:
LOAD *
FROM
ExampleData.xlsx
(ooxml, embedded labels, table is StartDateData);
//******New table********//
StartDate1:
LOAD Date_StartDate, Date_Id
FROM
ExampleData.xlsx
(ooxml, embedded labels, table is StartDateData);

//****************//
EndDate:
LOAD *
FROM
ExampleData.xlsx
(ooxml, embedded labels, table is EndDateData);
//****************//
JOIN (EndDate)
LOAD *
Resident StartDate1 ;
//**************//
Indicator:
LOAD
IF(EndDATE - Date_StartDate <= 15, 'Y', 'N') AS Gap_Indicator,
Date_Id,  Date_StartDate, EndDATE
RESIDENT EndDate;

DROP TABLE EndDate
DROP TABLE StartDate1;

[sunny_talwar · Accepted solution]

How about this?

StartDate:

LOAD *

FROM

ExampleData.xlsx

(ooxml, embedded labels, table is StartDateData);

EndDate:

LOAD *

FROM

ExampleData.xlsx

(ooxml, embedded labels, table is EndDateData);

TempTable:

NoConcatenate

LOAD *

Resident StartDate;

JOIN (TempTable)

LOAD *

Resident EndDate ;

Indicator:

LOAD ID,

  IF(EndDATE - StartDate <= 15, 'Y', 'N') AS Gap_Indicator

RESIDENT TempTable;

DROP Table TempTable;

[vinieme12]

there is a problem

Do you have a unique KEY that identifies each row uniquely before you do a JOIN
?

your ID = 5 , is repeated twice in start date tab, if you add another row for 5 with a date say 2/1/2016 you will end up having 6 rows of data for ID = 5

i.e. = 3(StartDateRecords) *  2 (EndDateRecords)