Linking tables and then using the value from other table.
Thanks for the reply. I thought it requires some mapping function or anything to do that. another problem what I am facing is, earlier i used to connect my QV to excel files and now I am doing that with the actual database. So , i just select the tables/fields that I want. But, if I need to use some transforamtion of the data, how could I do that? We cannot use statements like year(StartDate) in the Select statement.
What could be a way when you want to convert, transform etc your data , which is connected to a data source.?
Re: Linking tables and then using the value from other table.
I ALWAYS precede an SQL SELECT with a load statement, whether or not I have any transformation. But the load statement is where you can do your transformation. So for example:
LOAD MyID ,if(MyName='Bob','Bobby',MyName) as MyName ,date(date#(MyDate,'YYYYMMDD')) as MyDate ,year(date#(MyDate,'YYYYMMDD')) as MyDate ; SQL SELECT MyID ,MyName ,MyDate FROM MyDatabase ;
Mind you, I typically do not put anything but the date in the main table. I reserve the year, month and so on for a separate calendar table, linked by the date field.
The general rule is that a LOAD statement can always precede any other source, including an SQL SELECT or even another LOAD statement. So you can stack as many LOADs right on top of each other as you want.
LOAD * ,month(FirstDayOfNextMonth) as NextMonth ; LOAD * ,monthstart(OneMonthFromNow) as FirstDayOfNextMonth ; LOAD * ,addmonths(Date,1) as OneMonthFromNow ; LOAD date#(Date,'YYYYMMDD') as Date ; SQL SELECT Date FROM Database ;
Mind you, that's a silly example, but hopefully you get the idea. You can just keep adding fields like that. Or by not using LOAD *, and listing fields explicitly, you can remove fields, like if you created OneMonthFromNow just to create the later fields. A simpler way to drop fields, though, is probably just with the DROP FIELDS statement.