How to join two tables

[#1]

Hi,

I have two tables

Country1:

LOAD Country,

     Capital,

     [Area(km.sq)],

     [Population(mio)],

     [Pop. Growth],

     Currency,

     Inflation,

     [Official name of Country]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

and

Country2:

LOAD Country,

     Capital ,

     [Area(km.sq)] ,

     [Population(mio)] ,

     [Pop. Growth] ,

     Currency ,

     Inflation ,

     [Official name of Country]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

I want to get the capitals from two tables into a list box. Please let me know how can I create a composite key to solve this issue. Because I learner of Qlikview, I am not aware of the steps to get data from multiple table.

Please help me in getting the solution.

Thanks,

Sujeet

[swuehl]

QV will automatically concatenate the records of tables you are reading in, when the number of fields and the field names are the same in both tables. It looks like this is the case in your setting. So when executing the script, I think you are only getting one table in the data model, Country1, but the fields will contain data of both tables.

Creating a list box of field Capital should show all read in capitals.

Hope this helps,

Stefan

[#3]

Hi Stefan,

Thanks for answering. But I want to elaborate my problem further.

I have got a requirement like

Country1:

LOAD Country ,

     Capital ,

     [Area(km.sq)] ,

     [Population(mio)] ,

     [Pop. Growth],

     Currency ,

     Inflation,

     [Official name of Country]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Country2:

LOAD Country ,

     Capital ,

     [Area(km.sq)] ,

     [Population(mio)] ,

     [Pop. Growth] ,

     Currency ,

     Inflation ,

     [Official name of Country]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Country3:

LOAD Country ,

     [Official name of Country] ,

     [Area(km.sq)] as area3

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Once I reload the sricpt I get synthetic table. To resolve that I just renamed the common fields. So my doubt is how can i get the common fields which will include data from mutiple tables. Or as per your explanation QV will take care automatically.

My changed script is something like :

Country1:

LOAD Country as country1,

     Capital as capital1,

     [Area(km.sq)] as area1,

     [Population(mio)] as population1,

     [Pop. Growth],

     Currency as currency1,

     Inflation as inflation1,

     [Official name of Country] as off_name1

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Country2:

LOAD Country as country2,

     Capital as capital2,

     [Area(km.sq)] as area2,

     [Population(mio)] as population2,

     [Pop. Growth] ,

     Currency as currency2,

     Inflation as inflation2,

     [Official name of Country] as off_name2

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Country3:

LOAD Country as country3,

     [Official name of Country] as off_name3,

     [Area(km.sq)] as area3

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

So, if i want to create a list box for country and capital then how I am going to get data from multiple table.

Please guide me.

Thanks,

sujeet

[swuehl]

The only issue seems to be your third table, where some fields are missing.

You can force QV to also concatenate this table by using CONCATENATE LOAD prefix.

Country3:

CONCATENATE LOAD Country ,

     [Official name of Country] ,

     [Area(km.sq)] as area3

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Now you should only have one table in your data model, containing all records.

For sure, if you select a Country from source Country3.csv, you won't be able to see the Capital or other missing information.

You can use a where clause with exists() function to only load non previously loaded Countries.

Or maybe I am still missing your issue?

[#5]

Thanks Stefen.

It was a very good suggestion. I almost acheive what I am looking for. But one thing is that in the country list box I am getting some Junk values(nubmers). How can i remove that.

Thanks,

Sujeet

[swuehl]

I would try to first check if the junk values originate from your input data (csv files) or maybe from some error in your load script.

If you want to remove some lines from your input data, maybe like

LOAD

...

FROM   .....

WHERE istext(Country);