Is it possible to load from multiple sql files?

[#1]

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??

[jonasheisterkam]

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)

next

[#3]

Cant we use a statement like

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

[kaushiknsolanki]

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

     Next

Regards,

Kaushik Solanki

[#5]

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.

[kaushiknsolanki · Accepted solution]

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

     Next

Regards,

Kaushik Solanki

[#7]

This is awesome... Thanks!!!