topic Re: How to create a table with field values AND results of SQL Query? in QlikView

How to create a table with field values AND results of SQL Query?

Wed, 25 Nov 2020 16:16:04 GMT

I understand how to make a SQL query and save it as a table:

Table A:

SQL Select * From db_table

What I want to do is something like this

Table A:
[$(Variable1)], [$(Variable2)], [SQL Columns]

In other words, here I am performing a SQL query with variables as parameters. What I need to do is store records with columns for the variables (these will repeat obviously) and then associate them with the SQL query results. Fairly simple, just not sure I'm getting the syntax correct.

Any help would be greatly appreciated!

Thanks

Re: How to create a table with field values AND results of SQL Query?

Wed, 03 Aug 2016 18:48:22 GMT

Not sure if I understood correctly, but do you need something like this?

SET vTest = 'Test';

LOAD
*,
'$(vTest)' as newCol
;
SQL Select * From db_table;

Re: How to create a table with field values AND results of SQL Query?

Wed, 03 Aug 2016 18:55:15 GMT

Hi Jakub,

The idea is to perform a sql query based on parameters. We'll say (White, Black) and (Small, Large).

My table needs to look like

Color Size SQL_Field1 SQL_Field2

White Small 23.5 New York

Black Small 28.3 Maine

I've already got a table that just pulls the SQL fields, but I wondered how I might add the variable column to this result set.

Todays table:

SQL_Field1 SQL_Field2

23.5 New York

Does that make sense?

Thank you

Re: How to create a table with field values AND results of SQL Query?

Wed, 03 Aug 2016 19:13:38 GMT

Hey,

I do not understand why do you want to do it on SQL level if we can utilise Preceding Load and QlikView functions Sure - you can add calculated columns, but we must have some calculation conditions to create them. When it comes to Color column in your example, what would be the condition?

PS Even in sql this should work:

SQL Select
 *,
'$(variable)' as newSqlColumn

 From db_table;

BR,

Kuba

Re: How to create a table with field values AND results of SQL Query?

maxgro — Wed, 03 Aug 2016 19:18:27 GMT

2 examples

1) all query in a variable

set vSQL = '

select * from dwh_cci.d000_calendario_cci

where tip_anno = 2016 and tip_mese = 7 and tip_giorno = 31

;

$(vSQL)

;

drop table t;

2) a filter in a variable

set vparam = 2016;

t2:

select * from dwh_cci.d000_calendario_cci

where tip_anno = $(vparam) and tip_mese = 7 and tip_giorno = 31

;

Re: How to create a table with field values AND results of SQL Query?

Anonymous — Wed, 03 Aug 2016 19:20:14 GMT

I think you need to do it like that

SQL Select

"$(variable)" as newSqlColumn

From db_table;

Re: How to create a table with field values AND results of SQL Query?

Wed, 03 Aug 2016 19:22:18 GMT

Hi Jakub,

Maybe some more clarification on my part is necessary. I'm reading in one file that contains a list of combinations for these variables. So the table I'm reading in looks like:

Color Size List of Values

White Small [ABC, DEF, GHI]

White Large [JKL, ABC, LMN]

Black Small [DEF]

.... etc

For each of these combinations that I read in, I need to query the database where color = $(colorVariable) and Size = $(sizeVariable) and Value in ('$(List of Values)). Basically, for each combination I'm passing that list of values and getting results for those particular values.

In all honesty, I'm not sure how to write out a simple column that says "White" "Small" and then the results from the SQL query (where I expect one record per OrderID, the fact table grain).

When I make a sql Select statement, how do I add 'regular' columns to the resultset?

Can I just put them in the SQL statement as you've said above? B/c the color and size I already have loaded, I just need to write them to their own individual columns.

Apologies for the confusion, I'm sure I'm explaining it in a complicated way

Thank you!

Re: How to create a table with field values AND results of SQL Query?

maxgro — Wed, 03 Aug 2016 19:56:34 GMT

script

😧

load * inline "

Color; Size; LOV

White ; Small ; [ABC, DEF, GHI]

White ; Large ; [JKL, ABC, LMN]

Black ; Small ; [DEF]

" (delimiter is ';');

FOR i = 0 to NoOfRows('D') -1

LET c=Peek('Color', $(i));

LET s=Peek('Size', $(i));

LET lov=replace(PurgeChar(Peek('LOV', $(i)), '[]'), ',', chr(39) & ',' & chr(39));

//TRACE $(lov);

LET vsql =

'select * from table where color = ''$(c)'' and size = ''$(s)'' and lov in (''$(lov)'') '

;

TRACE $(vsql);

NEXT

trace

select * from table where color = 'White' and size = 'Small' and lov in ('ABC',' DEF',' GHI')

JKL',' ABC',' LMN

select * from table where color = 'White' and size = 'Large' and lov in ('JKL',' ABC',' LMN')

DEF

select * from table where color = 'Black' and size = 'Small' and lov in ('DEF')

Re: How to create a table with field values AND results of SQL Query?

Wed, 03 Aug 2016 20:44:37 GMT

Doesn't matter for this topic, but this will fail the script. It will tell sql to look for the column of the name specified in variable (and not to set variable's value AS a column)