Qlik Community

Qlik Healthcare User Group

Highlighted
Employee
Employee

"Show Me the Data"

You can't handle the Data.

Literally.

You've been asked to quickly build a prototype of data from a system that has hundreds of tables. You need to extract all of the data from all of the tables and store it into QVD files. Ouch that seems like a lot of manual typing.

Frankly I'm to lazy for that kind of work. So go ahead and ask me how I would approach that task?

I would take advantage of the tools that I have like SQL Server or Oracle and let them do what they do best? Maintain the lists of all of the tables they hold and run SQL (TSQL or PL/SQL) statements to show me those values. For SQL Server you can do something pretty simple like this to get a list of all of the tables in the database you are connected to:

Select * from Sys.Tables.

Big deal you say what does that have to do with your task of building a massive layer of QVD's. Well that's just the starting point. Imagine that the first table name it shows you is Patients. In Qlik we would need the following code in order to pull the data back, create a QVD and then remove the table.

Patients:

SQL select * from Patients with (nolock);

STORE Patients INTO Patients.qvd (qvd);

DROP TABLE Patients;

In case you never thought about it as I do ... what I see is blah-blah-blah boiler plate text "table name" blah-blah-blah "table name" yadda-yadda-yadda table name. So if most of the script to build basic layer 1 QVD's is just boiler plate then let's just wrap our query in SQL Server or Oracle with the boiler plate text.

select name + ':' + char(13) + char(10) + 'SQL select * from ' + name + ' with (nolock);' + char(13) + char(10) + 'STORE ' + name + ' INTO ' + name + '.qvd (qvd);' + char(13) + char(10) + 'DROP TABLE ' + name + ';' + char(13) + char(10) + char(13) + char(10)

from sys.tables

Crazy simple right. If I have 10 tables it builds out the syntax for me to create 10 QVD's. But if I have 100 tables it builds out the syntax for me to create 100 QVD's. Or a 1,000. The wonderful thing is the more tables in the database the more time I save myself.

If you are using SSMS (SQL Server Management Studio) you can simply tell it to display the results as TEXT instead of in Grid mode and then copy the results and paste it into Qlik.

SQLScriptToBuildQVD.jpg

"Show Me the Data!" No problem and while the script is running for all 1,273 tables I'll be enjoying some chocolate listening to the birds chirp.

**** Bonus like from me if you use Oracle and will paste the Oracle syntax to do the same thing here for others to take advantage of.

**** Bonus like from me if you build the second command ... you know the one that will actually do the LOAD statement from the QVD along with a preceeding load of the fields names so that we can paste it into our layer 2 QVD builder or our application. HINT - You the field names are stored in sys.columns but you knew that and I won't be mad if you use a cursor. Something like below so that we can comment out any fields we don't want, or rename them:

Patients:

LOAD PatientID,

     First_Name,

     Middle_Initial,

     Last_Name ....

FROM Patients.QVD (QVD);

4 Replies
marmentrout5
Contributor

Re: "Show Me the Data"

I was so close with a TSQL statement, but alas had to resort to a cursor .  There are still a few aesthetic things, but this may help with the second bonus.

DECLARE @TABLE_NAME AS NVARCHAR(255);

DECLARE @GenQlikLoad AS CURSOR;

SET @GenQlikLoad = CURSOR FOR

SELECT

  table_name

FROM INFORMATION_SCHEMA.TABLES;

--WHERE table_name LIKE '%<some wildcard search>%';

OPEN @GenQlikLoad;

FETCH NEXT FROM @GenQlikLoad INTO @TABLE_NAME;

WHILE @@fetch_status = 0

BEGIN

  SET NOCOUNT ON

  PRINT @TABLE_NAME + ':' + CHAR(13) + 'LOAD'

  SELECT

  CASE

  WHEN c.ORDINAL_POSITION = 1 THEN '     [' + COLUMN_NAME + ']'

  ELSE '    ,['+ c.COLUMN_NAME + ']'

  END

  FROM INFORMATION_SCHEMA.COLUMNS c

  WHERE c.TABLE_NAME = @TABLE_NAME

  ORDER BY c.ORDINAL_POSITION

  PRINT 'FROM ' + @TABLE_NAME + '.qvd (qvd);' + CHAR(13) +

'/////////////////////////////////////////////////////////////////////////////////////////////////'

  FETCH NEXT FROM @GenQlikLoad INTO @TABLE_NAME;

END

CLOSE @GenQlikLoad;

DEALLOCATE @GenQlikLoad;

Employee
Employee

Re: "Show Me the Data"

1 point off for not wrapping column names with [ ] or " " to handle field names that had spaces. But Bonus points for indenting the field names and double BONUS points for feeling guilty about using a Cursor. Triple bonus for even putting a commented out where clause to filter what tables to iterate through. 

I had to a use a Cursor to and felt equally bad ... for about 10 seconds. When I realized how powerful it was I cut myself some slack.

Congratulations on earning my undying respect (as if you didn't already have it). The power of what you just handed to others is pretty amazing in the right environments.

Can't wait to see who chips in by helping others with the Oracle versions.

marmentrout5
Contributor

Re: "Show Me the Data"

Damn it Jim!    Corrected the space in column names with brackets.  That was a fun foray back into TSQL.  Hope this provides value to others.  Keep up the great work Dalton!

marmentrout5
Contributor

Re: "Show Me the Data"

Thought I would add the code that dynamically generates LOAD scripts from SQLServer tables.

DECLARE @TABLE_NAME AS NVARCHAR(255);

DECLARE @GenQlikLoad AS CURSOR;

SET @GenQlikLoad = CURSOR FOR

SELECT

  table_name

FROM INFORMATION_SCHEMA.TABLES;

--WHERE table_name LIKE '%<some wildcard search>%';

--WHERE table_name IN ('table1','table2')

OPEN @GenQlikLoad;

FETCH NEXT FROM @GenQlikLoad INTO @TABLE_NAME;

WHILE @@fetch_status = 0

BEGIN

  SET NOCOUNT ON

  PRINT @TABLE_NAME + ':' + CHAR(13) + 'LOAD'

  SELECT

  CASE

  WHEN c.ORDINAL_POSITION = 1 THEN '     [' + column_name + ']'

  ELSE '    ,[' + c.column_name + ']'

  END

  FROM INFORMATION_SCHEMA.COLUMNS c

  WHERE c.table_name = @TABLE_NAME

  ORDER BY c.ORDINAL_POSITION

  PRINT ';'

  PRINT ' SQL SELECT'

  SELECT

  CASE

  WHEN c.ORDINAL_POSITION = 1 THEN '     [' + column_name + ']'

  ELSE '    ,[' + c.column_name + ']'

  END

  FROM INFORMATION_SCHEMA.COLUMNS c

  WHERE c.table_name = @TABLE_NAME

  ORDER BY c.ORDINAL_POSITION

  PRINT 'FROM ' + db_name() + '.' + SCHEMA_NAME() + '.' + @TABLE_NAME + ';'

  PRINT 'STORE ' + @TABLE_NAME + ' INTO  $(_local_layer1_qvd)L1_IL_' + @TABLE_NAME + '.qvd(qvd);'

PRINT '/////////////////////////////////////////////////////////////////////////////////////////////////'

  FETCH NEXT FROM @GenQlikLoad INTO @TABLE_NAME;

END

CLOSE @GenQlikLoad;

DEALLOCATE @GenQlikLoad;