Load if

[farolito20]

How Can I make this LOAD

LOAD

     MostRecent

Resident DimOld

Where exists (Checksum2, Checksum) and t_bpid = $(vCustomerNumber);

IF MostRecent=1 then

     LOAD

  t_bpid,

                               t_nama,

                               t_ccur,

                              ValidFrom,

                              ValidTill,

                              Checksum,

                              MostRecent

       Resident DimOld;

ELSEIF m=0 then

    LOAD

                              t_bpid,

                               t_nama,

                               t_ccur,

                              date(now()) as ValidFrom,

                              Timestamp#('31-12-2099 08:00:00','DD-MM-YYYY hh:mm:ss')          as ValidTill,

                               //date(now())                    as          ValidTill,

                              Checksum,

                              1 as MostRecent

Resident DimOld;

ENDIF

I need to use the MostRecent value, and then compare if is 1 or 0

[chematos]

Try this:

LOAD

                               t_bpid,

                               t_nama,

                               t_ccur,

                              IF(MostRecent = 1,ValidFrom, date(now())) as ValidFrom

                                       IF(MostRecent = 1,ValidFrom,

Timestamp#('31-12-2099 08:00:00','DD-MM-YYYY hh:mm:ss')) as ValidTill,

                              Checksum,

                              IF(MostRecent = 1, MostRecent, 1) as MostRecent

       Resident DimOld;

Hope this helps

[farolito20]

Ok, but probably I have many values with mostRecent=0, how can I do this just one time, even if I have 5 rows with mostrecent=0?

[chematos]

I don´t understand your question, sorry.

You could concatenate two tables using different where conditions if you prefer:

Load

  t_bpid,

                               t_nama,

                               t_ccur,

                              ValidFrom,

                              ValidTill,

                              Checksum,

                              MostRecent

       Resident DimOld

where MostRecent = 1;

concatenate

LOAD

                              t_bpid,

                               t_nama,

                               t_ccur,

                              date(now()) as ValidFrom,

                              Timestamp#('31-12-2099 08:00:00','DD-MM-YYYY hh:mm:ss')          as ValidTill,

                               //date(now())                    as          ValidTill,

                              Checksum,

                              1 as MostRecent

Resident DimOld

where MostRecent = 0;

[farolito20]

No, I probaby have this table

t_bpid,t_nama,t_ccur,ValidFrom,ValidTill,MostRecent

00001,Marina,USD,01/01/2012,15/06/2012,0

00001,Marina,COR,15/06/2012,20/10/2012,0

00001,Marina,COR,20/10/2012,01/01/2013,1

With the condition

IF(MostRecent = 1,ValidFrom, date(now())) as ValidFrom

         IF(MostRecent = 1,ValidFrom,Timestamp#('31-12-2099 08:00:00','DD-MM-YYYY hh:mm:ss')) as ValidTill,

The first 2 rows with MostRecent will load and I propably have this

t_bpid,t_nama,t_ccur,ValidFrom,ValidTill,MostRecent

00001,Marina,USD,02/01/2013,31/12/2099,1

00001,Marina,COR,02/01/2013,31/12/2099,1

00001,Marina,COR,20/10/2012,01/01/2013,1

So, I need just one new row, if exist MostRecent=1, load that and not continue looking if exist rows with MostRecent=0. But, if not exist rows with MostRecent=1, load just one row with MostRecent=0.

[farolito20]

something like "first 1 load"

[chematos]

I see that t_ccur has no part of primary key, so you could group by t_bpid and t_nama, so you will only have one register for this key.

Something like that:

Temp1:

Load distint

t_bpid,

t_nama,

sum(MostRecente) as recent

from F_TABLE

group by t_bpid, t_nama;

Recent1:

noconcatenate

Load *

resident Temp1

where recent =1;

left Join

load * from

F_TABLE;

Recent0:

noconcatenate

Load *

resident Temp1

where recent =0;

left Join

load

  t_bpid,

  t_nama,

  MostRecent as recent,

                               t_ccur,                             

                              date(now()) as ValidFrom,

                              Timestamp#('31-12-2099 08:00:00','DD-MM-YYYY hh:mm:ss')          as ValidTill,

                               //date(now())                    as          ValidTill,

                              Checksum,

                              1 as MostRecent

from

F_TABLE;

Final_Table:

noconcatenate

load * resident Recent1;

concatenate

load * resident Recent0;

Drop table Recent1, Recent0, Temp1;

[farolito20]

t_ccur is part of my key.

Thats my table, first I just had the 2 first rows.

When I did this

LOAD

  t_bpid,

  t_nama,

  t_ccur,

  IF(MostRecent = 1,ValidFrom, date(now())) as ValidFrom

          IF(MostRecent = 1,ValidFrom,

          Timestamp#('31-12-2099 08:00:00','DD-MM-YYYY hh:mm:ss')) as ValidTill,

   Checksum,

   IF(MostRecent = 1, MostRecent, 1) as MostRecent

       Resident DimOld;

I recieve two new rows,  because in the load I load all rows with the combination t_bpid,t_nama,t_ccur. In this case my new field is with t_ccur=USD.

Now, I need to load just one row with  this combination, so I do this:

LOAD

                    t_bpid,

                     t_nama,

                     t_ccur,

                     if(MostRecent=1,ValidFrom,date(now())) as ValidFrom,

                     if(MostRecent=1,ValidTill,Timestamp#('31-12-2099 08:00:00','DD-MM-YYYY hh:mm:ss')) as ValidTill,

                    Checksum,

                    IF(MostRecent = 1, MostRecent, 1) as MostRecent

          Resident DimOld

          Where exists (Checksum2, Checksum) and t_bpid = $(vCustomerNumber);

Checksum is my key with combination.

I need to load one row, if exist my key with MostRecent=1, load this and exit of load.

If not exist row with MostRecent=1, load just 1 one with MostRecent=0

[chematos]

Ok, I think you can use my last script using your primary key to group by, but concatenate Recent0 only if not exists that key in Recent1

[farolito20]

How I know the number of rows of a table?