topic Re: Problem with value into the field in QlikView

Problem with value into the field

pgalvezt — Wed, 11 Jul 2012 15:20:53 GMT

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

Re: Problem with value into the field

Wed, 11 Jul 2012 16:01:05 GMT

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.

Re: Problem with value into the field

pgalvezt — Wed, 11 Jul 2012 16:11:33 GMT

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.

Re: Problem with value into the field

Wed, 11 Jul 2012 16:26:38 GMT

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.

Re: Problem with value into the field

pgalvezt — Wed, 11 Jul 2012 16:33:58 GMT

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

Re: Problem with value into the field

Wed, 11 Jul 2012 17:03:53 GMT

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.

Re: Problem with value into the field

pgalvezt — Wed, 11 Jul 2012 18:42:40 GMT

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

Re: Problem with value into the field

pgalvezt — Wed, 11 Jul 2012 21:03:49 GMT

Suggestions?

Re: Problem with value into the field

Thu, 12 Jul 2012 03:51:10 GMT

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.

Re: Problem with value into the field

pgalvezt — Thu, 12 Jul 2012 13:28:19 GMT

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

Re: Problem with value into the field

Thu, 12 Jul 2012 14:18:02 GMT

Replace

rut&ApplyMap('MapBttcc11_Bin',left(cuenta,3),Null())

with

text(ApplyMap('MapBttcc11_Bin',left(cuenta,3),Null()))&text(rut)

Regards,

Kiran.

Re: Problem with value into the field

pgalvezt — Thu, 12 Jul 2012 14:31:52 GMT

Nice but the 0000 they don't want to disappear. I tried to change the format number but nothing happened. first number would be 762266797 and no 76000002266797 See JPG file.

Re: Problem with value into the field

Thu, 12 Jul 2012 14:46:47 GMT

try:

text(Num(ApplyMap('MapBttcc11_Bin',left(cuenta,3),Null()),'##'))&text(rut)

Just try to play around the number format and text functions.

Regards,

Kiran Rokkam.

Re: Problem with value into the field

pgalvezt — Thu, 12 Jul 2012 14:51:25 GMT

Got it!!!!

text

(ApplyMap('MapBttcc11_Bin',Num#(left(cuenta,2)),Null()))&text(rut) as Homologo1,
Num#(left(cuenta,2))&rut as Homologo_Btcc11

nice,

Many Thanks Karin