Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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??
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
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
Cant we use a statement like
SQL SELECT * From Data_*; or something like that?
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
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.
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
This is awesome... Thanks!!!