Load and compare date into variables and

[suzel404]

Hi all,

How can I load compare and load date ?

Example:

I want to load Table1 and Table2 where date.TABLE1 < date.TABLE2

LOAD

     Field,

     date

FROM TABLE1.qvd

LOAD

     Field,

     date2

FROM TABLE2.qvd

WHERE TABLE1.date < TABLE2.date2;

How can I do that ?

Thanks for your help.

[maxgro]

if you want to compare record with same Id field, this is the script

Table1:

LOAD

  Id,

  date,

  Area

FROM Table1.xls (biff, embedded labels, table is Sheet1$);

Map:

Mapping load Id, date#(date, 'DD/MM/YYYY  hh:mm:ss') Resident Table1;

Table2:

inner Keep (Table1)

LOAD

  Id,

  Date,

    Mag

FROM Table2.xlsx (ooxml, embedded labels, table is Sheet1)

where ApplyMap('Map', Id, date#('31/12/2999', 'DD/MM/YYYY')) < date#(Date, 'DD/MM/YYYY  hh:mm:ss')

;

otherwise please explain your logic to exclude records as I don't understand

[bgerchikov]

Hi Joshua,

In your samples I didn't find such scenario when Date from the table 2 is smaller than date in table 1.

The logic is to join both tables first into temp table, compare dates and join selection to the table 1:

Please review attached.

[suzel404]

Hi Boris and Massimo,

Sorry for my response. In fact I have a table1 of store with Openeddate and a table2 of open store.

I want to load in one table all the store still open, my clause (date.table1 < date.TABLE2).

So, I get all store opened.

table1

LOAD

id, Date

from table1

inner join

load

id,

date2

from table2

store table1 into table1_tmp.qvd

Load * from table1_tmp.qvd

where date(date) < date(date2);

Is there a optimize way to load these tables ?

Thanks for your help.

[jagan · Accepted solution]

Hi,

Instead of storing into qvd you can do like this

table1_Temp:

LOAD

id, Date

from table1;

inner join (table1_Temp)

load

id,

date2

from table2;

Table1:

NoConcatenate

Load *

from table1_Temp

where date(date) < date(date2);

DROP TABLE table1_Temp;

Regards,

Jagan.

[#6]

Hi,

Please try below

Tab:

LOAD

     Field,

     date

'Table1' as Flag

FROM TABLE1.qvd

concatenate

LOAD

     Field,

     date2,

'Table2' as Flag

FROM TABLE2.qvd

TAB2:

LOAD

     Field,

     date

     Flag

resident where date(date)<date(date2);

drop table TAb1;

[maxgro]

if you want just one table instead of two replace keep with join in my previous post; new script (difference in bold) is above;

with this script it seems to me you don't need to store and read a qvd

Table1:

LOAD

  Id,

  date,

  Area

FROM Table1.xls (biff, embedded labels, table is Sheet1$);

Map:

Mapping load Id, date#(date, 'DD/MM/YYYY  hh:mm:ss') Resident Table1;

//Table2:

//inner Keep (Table1)

inner join (Table1)

LOAD

  Id,

  Date,

    Mag

FROM Table2.xlsx (ooxml, embedded labels, table is Sheet1)

where ApplyMap('Map', Id, date#('31/12/2999', 'DD/MM/YYYY')) < date#(Date, 'DD/MM/YYYY  hh:mm:ss')

;

or perhaps you want to compare by third field and not id?

Table1:

LOAD

  Id as Id1,

  date,

  Area as Store

FROM Table1.xls (biff, embedded labels, table is Sheet1$);

Map:

Mapping load Store, date#(date, 'DD/MM/YYYY  hh:mm:ss') Resident Table1;

Table2:

inner Keep (Table1)

LOAD

  Id as Id2,

  Date,

    Right(Mag,4) as Store

FROM Table2.xlsx (ooxml, embedded labels, table is Sheet1)

where ApplyMap('Map', Right(Mag,4), date#('31/12/2999', 'DD/MM/YYYY')) < date#(Date, 'DD/MM/YYYY  hh:mm:ss')

;

[suzel404]

Many thanks for your answer!!