Help for this data extraction

[#1]

Hi People,

Using the following code I got these two tables:

I would take all the practices that have state 40 but did not have a lower state insert after (looking at the date and time). In this case, the control that I want should not return the practice 349 because after the state 40 was added an inferior status (30) more recently .

I try this control selecting the practices with state 40 and veryfing if the date of the practices that are minor of 40 are major of the date of the practices that are the state 40 (the same for the time). I think that the reasoning is correct, but the control didn't work (prolly for my mistakes). The code is below:

QlikView respond that can not find ''DataPratica40'' and ''OraPratica40''.

Can someone help me? Sorry for bad English

[CarlosAMonroy · Accepted solution]

Hi Lorenzo,

In fact QV can't find the fields because they do not exist in the 'where' clause that is in the table used as join.

You can do what you want to do in different ways.

Example is:

NoConcatenate

TableTmp:

Load FieldNames

Resident Table1;

join (TableTmp)

Load FieldNames,

Resident Table2;

drop table Table1, Table2;

NoConcatenate

Table3:

Load FieldNames

Resident TableTmp

Where Conditions;

drop table Table3;

Thanks, hope it helps

Carlos M

[aarkay29]

Seems like "DataPratica40'' and ''OraPratica40''.that you are using in your Second Table i.e. from resident table Tabella1  here does not exist

Clearly the two fields exist in tabella2. But you are using them from resident tabella1 which is giving you an error

[#4]

It works. The result is this:

How can I do the comparison between the fields DataTmp AND DataPratica40 and the fields OraTmp AND OraPratica40? I try this:

But the result is an empty table. I want to get all the practices that have as their last state the State 40 (For Example, I do not want the practice 349 because It have as the last state the State 30, checking the time).

Thanks for the help

[CarlosAMonroy]

Hi Lorenzo,

What I see is that the table result by the join, is not bringing any links between the tables.

So first try to do a left join instead of the join or concatenate. Do it by the Keys that are the same in both tables.

Thanks,

Carlos

[#6]

Hello Carlos,

Before I did this:

If I do a Left Join instead the Join I lost the practice 349 that have the State 40 (that is the one that interests me)

Thanks