Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Help for this data extraction

Hi People,

Using the following code I got these two tables:

Minori40.JPG 40.JPG

SQL.JPG

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:

Control.JPG

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

Can someone help me? Sorry for bad English

1 Solution

Accepted Solutions
CarlosAMonroy
Contributor III

Re: Help for this data extraction

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

5 Replies
CarlosAMonroy
Contributor III

Re: Help for this data extraction

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
Valued Contributor

Re: Help for this data extraction

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




Not applicable

Re: Help for this data extraction

It works. The result is this:

Cattura.PNG

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

Cattura.PNG

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
Contributor III

Re: Help for this data extraction

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

Not applicable

Re: Help for this data extraction

Hello Carlos,

Before I did this:

Cattura.PNG

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