topic Is it possible to load from multiple sql files? in QlikView

Is it possible to load from multiple sql files?

Tue, 12 Nov 2013 06:07:51 GMT

Hi guys,

I have multiple tables in my sql server for data on a weekly basis.

The tables are named Data_week1, Data_week2 etc. is it possible to load the entire data in one single go??

Re: Is it possible to load from multiple sql files?

jonasheisterkam — Tue, 12 Nov 2013 06:19:13 GMT

Test the Union command in the sql part.

SELECT City FROM Customers

UNION ALL

SELECT City FROM Suppliers

ORDER BY City;

With this you can do nearly the same as Auto-concatenate do in Qlikview.

Or Write a For loop like

For i=1 to 10

load

from Table$(i)

Re: Is it possible to load from multiple sql files?

Tue, 12 Nov 2013 07:07:35 GMT

Cant we use a statement like

SQL SELECT * From Data_*; or something like that?

Re: Is it possible to load from multiple sql files?

kaushiknsolanki — Tue, 12 Nov 2013 07:09:52 GMT

Hi,

You cant use the * to load all the tables.

Instead you can use below script.

for i=1 to 10

Sql Select * from Data_$(i);

Regards,

Kaushik Solanki

Re: Is it possible to load from multiple sql files?

Tue, 12 Nov 2013 12:11:39 GMT

Hi Kaushik,

So my monthly data tables are in the format Data-July-2013 , Data -August-2013 etc. So i want to loop it to take data fully. and this needs to be repeated every month and should go on when we reach 2014. The start month is july 2013.

How can i add months and years into a for loop and work this out.

Re: Is it possible to load from multiple sql files?

kaushiknsolanki — Tue, 12 Nov 2013 12:20:41 GMT

Hi,

First you need to create the Date's using Master Cal.

For example

Let vMin = num(Makedate(2013,07,01));

let vMax = num(makedate(2013,12,31));

Temp_Cal:

load Date(floor(Monthend(Date($(vMin)+rowno()-1))),'MMMM-YYYY') as Date

Autogenerate 1

while Date($(vMin)+rowno()-1) < Date($(vMax));

Month:

Load Distinct Date as Month Resident Temp_Cal;

Let vCount = NoOfRows('Month');

For i = 1 to $(vCount)

Let vMonth = peek('Month',$(i),'Month');

Sql Select * from Data_$(vMonth);

Regards,

Kaushik Solanki

Re: Is it possible to load from multiple sql files?

Wed, 13 Nov 2013 06:53:55 GMT

This is awesome... Thanks!!!