topic Linking tables and then using the value from other table. in QlikView

Linking tables and then using the value from other table.

Tue, 28 Jun 2011 21:00:36 GMT

Hello everyone,

I am using a table that includes data of all the products and associated details of them. This is the main table with the product ID as Table1.

Now, I have another table with just a prodID and Region and some other values. when I load both the tables, QV join both the tables with prodID.

Now, if I want to create a graph with the values from Table 2 , I mean taking prod ID from table 2 and some of the columns from Table 1 corresponding to prodID in table 2. How can I do that?

I know its a very basic question. Sorry for posting.

Thank you.

Linking tables and then using the value from other table.

johnw — Tue, 28 Jun 2011 21:11:22 GMT

Just create a graph with fields from both tables. There's nothing special or fancy to do. QlikView understands the relationship between the two tables, and should give you the results you expect.

And you're in the New to QlikView forum, so there's no need to apologize for basic questions. That's what this forum is for!

Linking tables and then using the value from other table.

Tue, 28 Jun 2011 22:53:06 GMT

Hey John,

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

Thanks. Appreciate your reply.

Re: Linking tables and then using the value from other table.

johnw — Tue, 28 Jun 2011 23:11:59 GMT

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.