Problem with value into the field

[pgalvezt]

Hello!

I have to emulate a vlookup excel in QlikView fot that I have 5 Tables (3 Excel and 2 tables Visual Fox Pro).

First: When I load the visual Fox tables appears a date like 1899 and bring me a lot of data. (Out of memory Warning).

Second:  Im creating 2 fields "Homologo_Btcc11" and "Homologo_Bsc01". "Homologo_Bsc01 is done from script" was easy because the two fields cc_cli and rut both were in the same table (Bsc01).

"Homologo_Btcc11" was created like:  (Here join rut plus cc_cli) like 22345569872 without -digit verificator

TempTabla:

Load

  CC_Cli   as CC_Cli

  resident Clasificaciones;

Left Join(TempTabla)

Load

rut

Resident BTCC11; 

Left Join(BTCC11)

Load CC_Cli&''&rut as Homologo_Btcc11

resident TempTabla;

Drop Table TempTabla;

But when execute this appear a warning like "Out of virtual And/Or Logical memory"

I Attached a jpg file with the What I want to do...."Homologo_Btcc11" was created like expression but I need it like..... as "Homologo_Btcc11" into the script similar to "Homologo_Bsc01". So after that I can do the vlookup:

MapFecha_Protesto:
Mapping Load "Homologo_Bsc01",Fecha_Protesto Resident BSC01;

Join(Btcc11)
Load ,ApplyMap('MapFecha_Protesto',"Homologo_Btcc11",Null()) as FechaProtesto Resident Btcc11;

- So What I want is delete "1899" Value into the field and "Out of virtual And/Or Logical memory" is going to dissapear I think

- second I need to create this "Homologo_Btcc11".

I attached 2 qvs example for better comprehension I hope you can help m with this.

Thank you

[#2]

Try the following:

MapFecha_Protesto:
Mapping Load Homologo_Bsc01,Fecha_Protesto Resident BSC01;

Join(Btcc11)
Load Homologo_Btcc11,ApplyMap('MapFecha_Protesto',"Homologo_Btcc11",Null()) as FechaProtesto Resident Btcc11;

Regards,

Kiran Rokkam.

[pgalvezt]

Hi Kiran thank you for your answer but I'm affraid that you dont understood the problem. because maybe I didn't explain very well the thing is that "Homologo_Btcc11" doesn't exit yet because when I created this join ("Homologo_Btcc11") appears a warning "Out of virtual And/Or Logical memory" and QlikView down. So I need to create first "Homologo_Btcc11" = CC-Cli (Field Clasificaciones) and Rut(Btcc11). and then do the vlookup. see the example QlikView please.

Thanks.

[#4]

I think, I get the picture now. In the first two statements there is a problem with the join. Lets say if your first table has 500 rows and second has 200 rows the join would result in 500x200=100,000 rows. Since rows get multplied if there are not common fields, depending on the no.of records in the each of the tables the memory for creating new table's is needed. Probably the volume is so huge that the machine ran out of memory.

I will go through the attachments and see what needs to be done.

Kiran.

[pgalvezt]

Thanks Kiran, I hope you can fix the problem. I'll ckecking the updates.

[#6]

Hi,

I noticed "Bttcc11_Bin" as common field in tables Clasificaciones and BTCC11. Hence created a mapping load on table "Clasificaciones" to attach CC_Cli with  table "BTCC11".

Note: Code changes in 47 and 73. Commented lines 230-248.

For the date issue in BTCC11, its better you filter unnecessary dates in SQL Where clause.

Kiran Rokkam.

[pgalvezt]

Yes. But how can I join CC_Cli and rut from Btcc11? I need to join it because I have to build:

num(`cc_cli`)&rut     as Homologo_Btcc11, (rut from btcc11- CC_Cli From Clasificaciones). and after that can do vlookup.

Best

[pgalvezt]

Suggestions?

[#9]

pgalvezt,

I the attached qvw, the field concatenation is joined is made in line 73. ApplyMap can be used not just as vlookup but also to get the reference fields.

Next steps would be to proceed for Fecha_Protesto.

Regards,

Kiran Rokkam.

[pgalvezt]

Hi Kiran, when I did the reload step QlikView show me rutccli+cuenta like a 123145678912.0000. I forgot to tell you that CC_Cli come from a excel file so Homologo must be rut (23558956) plus cc_cli (see clasificaciones xls file). final result: 2123558956

best