2

Because of business reasons I can’t specify queries. Also, I cannot create new views, tables, I only have read-only acces to the Datasource.

So, the problem is that I have a view with an extremely huge query behind. This cannot be changed. Then, I have a query that takes data from this view. Let’s call this LONGQUERY. (Running it is extremely long - I cannot change the query)

Then, this select query is the base of several smaller queries (so all of them are like “select [...] from LONGQUERY) - nearly 30 of them, but all of these small queries have the same columns.

How do I only query LONGQUERY once?

iSpain17
  • 2,502
  • 3
  • 17
  • 26

1 Answers1

4

If I understand what you're after, you can store the whole LongQuery to a file and then query that - or do a resident load (either can be faster - it depends on exactly what you're doing).

You'd do something like

LongQuery: SELECT * FROM LONGQUERY;
Store LongQuery into [c:\Data\LongQuery.qvd] (qvd);
Drop Table LongQuery;
SmallQuery1: LOAD field1, field2 from [c:\Data\LongQuery.qvd] (qvd) where A=1;
SmallQuery2: LOAD field1, field2 from [c:\Data\LongQuery.qvd] (qvd) where B=1;

or

LongQuery: SELECT * FROM LONGQUERY;
SmallQuery1: LOAD field1, field2 Resident LongQuery where A=1;
SmallQuery2: LOAD field1, field2 Resident LongQuery where B=1;
Drop Table LongQuery;

That said, I'm not sure why you'd do 30 sub queries with the same fields - aren't they possible in one query with multiple filters? In both scenarios above, you'd actually end up with just 1 table "SmallQuery1" as Qlik would automatically concatenate the two tables with the same fields in...

Is that what you're after?

Longer term, best practice would be to split your reload process into two parts - have one file that does the select & store from LONGQUERY and another that does the rest of the processing from the QVD file(s) that this saves out - that way you don't have to re-query LONGQUERY when you're doing dev work on the 30 other queries... this is the beginnings of a multi-tier architecture which is very common in Qlik.

x3ja
  • 928
  • 5
  • 19
  • The reason why I need 30 queries is the following: imagine that you have a table which stores the amount of fruits you have. So, normally you would have a column ‘fruit type’, and a column ‘quantity’. Now, instead of that I have columns Apple, Orange, Lemon, Watermelon, which all have their respective quantities in them. So, I have to convert them to the 1st version. On another note, I’ll try your solution! – iSpain17 Jul 17 '18 at 08:20
  • Thank you for your solution, it seems to be working with some modification! (I needed the LOAD statement for Residency) – iSpain17 Jul 17 '18 at 08:35
  • You might want to look at the crosstable script function (also known as unpivot) to sort out your columns in one go though... that is a function takes columns and breaks them into rows. Often done for months or similar across the top but would work for your fruits... https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/crosstable.htm – x3ja Jul 17 '18 at 11:57