topic Re: How to use an earlier retrieved column in previous SQL? in QlikView

How to use an earlier retrieved column in previous SQL?

Mon, 09 Sep 2013 05:59:58 GMT

Hi there,

May I know how to use an earlier retrieved column in previous SQL?

E.g.

Load

vv_c,

vr_create_dt,

vr_type_c,

vr_remark_x

FROM

[Data\vessel_remark.csv]

Then in another SQL, I want to use say earlier retrieved vv_c.

select "CREATE_DT", "TYPE_C","REMARK_X"
from "VESSEL_REMARK"

where "VESSEL_REMARK"."VV_C" = EARLIER_RETRIEVED_VV_C

How to retrieve the EARLIER_RETRIEVED_VV_C ??

Thanks

Re: How to use an earlier retrieved column in previous SQL?

tresB — Mon, 09 Sep 2013 06:11:12 GMT

Store the vaule in a variable like:

Let vRemark=Peek('vv_c');

And then try using this variable in the SQL where cluase.

Re: How to use an earlier retrieved column in previous SQL?

Mon, 09 Sep 2013 06:17:24 GMT

Do you mean something like this?

Let vRemark = Peek('vv_c');

select "CREATE_DT", "TYPE_C","REMARK_X"
from "VESSEL_REMARK"

where "VESSEL_REMARK"."VV_C" = $(vRemark)

Re: How to use an earlier retrieved column in previous SQL?

tresB — Mon, 09 Sep 2013 06:23:18 GMT

Try putting single quotes around the variable expansion like : "VESSEL_REMARK"."VV_C" = '$(vRemark)';

Remeber, here your variable will hold the last value in that field. If you want to take all the values in the field 'vv_c', you have to use EXISTS() function, but that would not work in SQL part.

Re: How to use an earlier retrieved column in previous SQL?

Mon, 09 Sep 2013 06:25:54 GMT

Remeber, here your variable will hold the last value in that field.

I actually need the whole set of values in field vv_c. That means I want to retrieve data for all those vv_c records, not just the last value. How to achieve in this case?

Re: How to use an earlier retrieved column in previous SQL?

orital81 — Mon, 09 Sep 2013 06:27:01 GMT

Hi Steve

The suggestions below works good as long as you have only 1 previous value.

If you have more you will need a loop, but this is hard work and similar to simple Join.

This lead to the question : Is there any reason you don't join both tables?

Re: How to use an earlier retrieved column in previous SQL?

tresB — Mon, 09 Sep 2013 06:39:06 GMT

In that case you have to load all the data from DB using SQL, then filter in qv using exists() like:

Load

vv_c,

vr_create_dt,

vr_type_c,

vr_remark_x

FROM

[Data\vessel_remark.csv]

FilteredData:

Load * Where Exists(VV_C,vv_c);

select "CREATE_DT", "TYPE_C","REMARK_X", "VV_C"
from "VESSEL_REMARK";

Re: How to use an earlier retrieved column in previous SQL?

Thu, 19 Sep 2013 10:44:17 GMT

Hi Ori,

I did use a LEFT JOIN but the performance was horrible.

Static_Info_1:

SQL select "VOYAGE"."ABBR_VESSEL_M", "VOYAGE"."VV_C", "SERVICE_C", "ETB_DT", "ETU_DT", "VOYAGE_IN_N", "VOYAGE_OUT_N", "BERTH_SEQ_N", "BERTH_M", "CNTR_CAPACITY_Q","HATCH_COVER"."TYPE_C" AS "HATCH_COVER","BERTH_ALLOCATION"."TERMINAL_C", "VESSEL"."CLASS_N"

from "VOYAGE", "BERTH_ALLOCATION", "BERTH","VESSEL", "HATCH_COVER"

where "VOYAGE"."VV_C" = "BERTH_ALLOCATION"."VV_C" and "BERTH_ALLOCATION"."ETB_DT" > trunc(sysdate-4) and "BERTH_ALLOCATION"."ETB_DT" <= trunc(sysdate+5) and

"BERTH_ALLOCATION"."TERMINAL_C" = "BERTH"."TERMINAL_C" and "BERTH_ALLOCATION"."SECTION_N" = "BERTH"."SECTION_N" and

"BERTH_ALLOCATION"."BERTH_N" = "BERTH"."BERTH_N" and "VOYAGE"."ABBR_VESSEL_M" != 'BANGKOK E' and "VESSEL"."VESSEL_ID_N" = "VOYAGE"."VESSEL_ID_N"

and "VOYAGE"."ABBR_VESSEL_M" = "VESSEL"."ABBR_VESSEL_M" and "VESSEL"."CLASS_N" = "HATCH_COVER"."CLASS_N";

Static_Info_2:

LEFT JOIN(Static_Info_1)

SQL select "STD_BAY_N", "BAY_N", "SIZE_Q", "LCG_Q" from "VESSEL_BAY";

The column to join is "CLASS_N"

What did I do wrong here?

===========================================================================================

I looked up the reference manual of QlikView. It says:

select [ all | distinct | distinctrow | top n [ percent ] ] *fieldlist

from tablelist

[ where criterion ]

[ group by fieldlist [ having criterion ] ]

[ order by fieldlist [ asc | desc ] ]

[ ( inner | left | right | full ) join tablename on fieldref = fieldref

]

But when I run this particular SQL, it says command not properly ended.

from "VOYAGE", "BERTH_ALLOCATION", "BERTH","VESSEL", "HATCH_COVER"

where "VOYAGE"."VV_C" = "BERTH_ALLOCATION"."VV_C" and "BERTH_ALLOCATION"."ETB_DT" > trunc(sysdate-4) and "BERTH_ALLOCATION"."ETB_DT" <= trunc(sysdate+5) and

"BERTH_ALLOCATION"."TERMINAL_C" = "BERTH"."TERMINAL_C" and "BERTH_ALLOCATION"."SECTION_N" = "BERTH"."SECTION_N" and

"BERTH_ALLOCATION"."BERTH_N" = "BERTH"."BERTH_N" and "VOYAGE"."ABBR_VESSEL_M" != 'BANGKOK E' and "VESSEL"."VESSEL_ID_N" = "VOYAGE"."VESSEL_ID_N"

and "VOYAGE"."ABBR_VESSEL_M" = "VESSEL"."ABBR_VESSEL_M" and "VESSEL"."CLASS_N" = "HATCH_COVER"."CLASS_N" left join "VESSEL_BAY" on "CLASS_N" = "VESSEL"."CLASS_N";

Thanks.

Re: How to use an earlier retrieved column in previous SQL?

Thu, 19 Sep 2013 10:58:38 GMT

Hi tresesco,

Base on your suggestion, I tried the following but I hit error. How do I load all the values in say "ABBR_VESSEL_M" for use in the next SQL in this case.

Static_Info_1:

from "VOYAGE", "BERTH_ALLOCATION", "BERTH","VESSEL", "HATCH_COVER"

where "VOYAGE"."VV_C" = "BERTH_ALLOCATION"."VV_C" and "BERTH_ALLOCATION"."ETB_DT" > trunc(sysdate-4) and "BERTH_ALLOCATION"."ETB_DT" <= trunc(sysdate+5) and

"BERTH_ALLOCATION"."TERMINAL_C" = "BERTH"."TERMINAL_C" and "BERTH_ALLOCATION"."SECTION_N" = "BERTH"."SECTION_N" and

"BERTH_ALLOCATION"."BERTH_N" = "BERTH"."BERTH_N" and "VOYAGE"."ABBR_VESSEL_M" != 'BANGKOK E' and "VESSEL"."VESSEL_ID_N" = "VOYAGE"."VESSEL_ID_N"

and "VOYAGE"."ABBR_VESSEL_M" = "VESSEL"."ABBR_VESSEL_M" and "VESSEL"."CLASS_N" = "HATCH_COVER"."CLASS_N";

Load * where exists (CLASS_N, "ABBR_VESSEL_M");

Re: How to use an earlier retrieved column in previous SQL?

orital81 — Sat, 21 Sep 2013 18:54:54 GMT

Hi Steve

I suggest to run the SQL first on a common SQL tool (SQL Server, PLSQL Developer, or any other tool).

There you will be able to get a more detailed error for youe querry and analyse what is wrong.

on which database do you run the querry (Orvacle / SQL Server?)

Also, why don't you select the raw data of the tables into QVD and then join QVDs?