topic Re: Load if in QlikView

Load if

farolito20 — Mon, 31 Dec 2012 15:53:43 GMT

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

Re: Load if

chematos — Wed, 02 Jan 2013 11:56:22 GMT

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

Re: Load if

farolito20 — Wed, 02 Jan 2013 13:53:49 GMT

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?

Re: Load if

chematos — Wed, 02 Jan 2013 14:12:10 GMT

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;

Re: Load if

farolito20 — Wed, 02 Jan 2013 14:21:10 GMT

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.

Re: Load if

farolito20 — Wed, 02 Jan 2013 14:39:46 GMT

something like "first 1 load"

Re: Load if

chematos — Wed, 02 Jan 2013 14:46:38 GMT

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;

Re: Load if

farolito20 — Wed, 02 Jan 2013 15:00:50 GMT

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

Re: Load if

chematos — Wed, 02 Jan 2013 15:18:44 GMT

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

Re: Load if

farolito20 — Wed, 02 Jan 2013 15:29:50 GMT

How I know the number of rows of a table?

Re: Load if

chematos — Wed, 02 Jan 2013 15:39:44 GMT

let rows = NoofRows(TableName);

Re: Load if

farolito20 — Wed, 02 Jan 2013 15:40:58 GMT

How load just the last row?

Re: Load if

chematos — Wed, 02 Jan 2013 15:52:51 GMT

add this field to your table:

Table:

Load *,

RowNo() as RowNumber

from whereever;

let rows= noofrows(Table);

Load *

resident table

where RowNo = $(rows);

Re: Load if

farolito20 — Wed, 02 Jan 2013 15:58:15 GMT

In the last where, missing () in RowNo?

Re: Load if

chematos — Wed, 02 Jan 2013 15:59:01 GMT

Load *

resident table

where RowNumber = $(rows);

Re: Load if

farolito20 — Wed, 02 Jan 2013 16:02:19 GMT

I have an error

Re: Load if

chematos — Wed, 02 Jan 2013 16:50:17 GMT

let rows= noofrows(Table);

What's the value of 'rows' variable??

Re: Load if

farolito20 — Wed, 02 Jan 2013 16:55:10 GMT

The variable don't save anything.

I can solve my problem to first 1 load and put order by ValidTill desc and this show me the last row

Re: Load if

chematos — Wed, 02 Jan 2013 16:56:28 GMT

let rows= noofrows('Table'); // may be you need to use simple quotes ...