topic Re: Join with no concatenation in QlikView

Join with no concatenation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Join with no concatenation

MindaugasBacius — Tue, 06 Dec 2016 14:59:06 GMT

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.

Re: Join with no concatenation

vinieme12 — Tue, 06 Dec 2016 15:13:46 GMT

Can you attach your ExampelData file?

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

Re: Join with no concatenation

Anonymous — Tue, 06 Dec 2016 15:18:58 GMT

Attached.

Re: Join with no concatenation

sunny_talwar — Tue, 06 Dec 2016 15:21:52 GMT

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

Re: Join with no concatenation

Anonymous — Tue, 06 Dec 2016 15:29:36 GMT

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;

Re: Join with no concatenation

sunny_talwar — Tue, 06 Dec 2016 15:36:55 GMT

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;

Re: Join with no concatenation

vinieme12 — Tue, 06 Dec 2016 15:42:43 GMT

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)