Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to use an earlier retrieved column in previous SQL?

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

9 Replies
tresesco
Not applicable

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

Store the vaule in a variable like:

Let vRemark=Peek('vv_c');

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

Not applicable

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


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)

tresesco
Not applicable

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

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.

Not applicable

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

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?

orital81
Not applicable

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

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?

tresesco
Not applicable

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


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";

Not applicable

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

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.

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" left join "VESSEL_BAY" on "CLASS_N" = "VESSEL"."CLASS_N";

Thanks.

Not applicable

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

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:

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";

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

orital81
Not applicable

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

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?