topic Re: load data (without join) in QlikView

load data (without join)

yelin_nyu — Sun, 20 Jan 2013 20:58:00 GMT

how to load only records (on order id and load id) from first tbl in my 2nd tbl without join? I want to create qvds for 2 tbls. after load in the 1 st tbl, i want to load all orders from1st in my 2nd tbl.

would an resident tbl work in this case?

tbl1:

sql select * from tbl1;

temp:

load orderid, loadid resident tbl1;

tbl2:

left join (temp)

orderid,

loadid,

from tbl2;

Re: load data (without join)

Gabriel — Sun, 20 Jan 2013 22:46:52 GMT

Hi,

Have you tried ApplyMap() ?

Re: load data (without join)

yelin_nyu — Sun, 20 Jan 2013 22:59:59 GMT

how do i use applymap()? i thought applymap can only be used on 1 variable. I need to match orderid and loadid.

Re: load data (without join)

Mon, 21 Jan 2013 05:15:39 GMT

try this

BY USING MAPPING----

tb1:

Mapping // in mapping table take first field as primarykey and 2nd field as map field name

LOAD

orderid, // first field must be primary key common in both table

ordername // this is the map field

FROM tb1;

tb2:

LOAD orderid,

loadid

Item,

[Unit Price],

Quantity,

Discount,

ProductID,

ApplyMap('tb1',orderid) as ordername

FROM tb2;

*******************************************************************************************************

BY USING JOIN----

tb1
orderid	ordername
1	aa
2	cc
3	ee

tb2
orderid	loadid
1	x11
4	y11

load* from tb1;

outer join

load * from tb2;

then output show like this

Joined table
orderid	ordername	loadid
1	aa	x11
2	cc	-
3	ee	-
4	-	y11

*************************************************************************************************************************

BY USING CONCATENATE-----

Table1
ID	VALUE
101	5
102	6
103	7

Table2
ID	VALUE
103	8
104	10

LOAD * from table1;

CONCATENATE(table1)

LOAD* from table2 WHERE NO EXIXTS(ID);

THEN OUTPUT SHOW LIKE THIS

Joined table
ID	VALUE
101	5
102	6
103	8
104	10

Re: load data (without join)

Gabriel — Mon, 21 Jan 2013 09:56:15 GMT

Hi,

It is possible to use multiple ApplyMap().

Re: load data (without join)

yelin_nyu — Tue, 22 Jan 2013 08:17:35 GMT

Hi, I really appreciate the help, but I don't think it's going to work in my case. The database structure is fairly complex and extremely huge which is the reason i tried to extract each tbl and store them as seperate qvds in the first place. With your methods suggested above, it would not run. If anyone knows a way that would require min joins, please let me know.

with the resident table i was trying, (i tried it with an overly simplified excel spreadsheet, it works. but when i used it in my actual tool, it doesn't) too many records are getting extracted from 2nd tbl. I had to abort it because it's getting out of control. will indexing the table solve the problem?

Re: load data (without join)

er_mohit — Tue, 22 Jan 2013 09:01:04 GMT

see the attached files