topic Re: mapping load and applymap() in QlikView

mapping load and applymap()

Thu, 20 Mar 2014 15:51:19 GMT

Hi,

Can anyone tell me if anything wrong in the script?

I want to add a field CREATED_DATE to the tbl (Tbl)

Though it's working fine but seeing only one value for one %RULE_ID

temp_RULE_DATE:
LOAD %RULE_ID,
CREATED_DATE
FROM
[..\..\External Data\Rule CREATED DATE.xlsx]
(ooxml, embedded labels, table is Sheet1);

Map_RULE_DATE:
Mapping
LOAD %RULE_ID,
CREATED_DATE
Resident temp_RULE_DATE;

Tbl:
load %RULE_ID,
     PRODUCT_NAME,
     PRODUCT_CATEGORY,
     CITY,
     STATE
ApplyMap('Map_RULE_DATE',%RULE_ID)as Created_Date

from [..QVD\Tbl.qvd](qvd);

Thanks.

Re: mapping load and applymap()

MK_QSL — Thu, 20 Mar 2014 15:54:03 GMT

Try this.... let us know whether you are getting error while running the script or script is ok but not receiving anything at UI end.

Let us know the script error...

ApplyMap('Map_RULE_DATE',%RULE_ID,'Unknown')as Created_Date

Re: mapping load and applymap()

pgrenier — Thu, 20 Mar 2014 15:57:10 GMT

Hello,

As your temp_RULE_DATE and Map_RULE_DATE tables have the same structure (same columns), I believe the second load statement tries to concatenate the results in the first table rather than creating the mapping element you desire.

Maybe you could simply try to directly load from the Excel file rather than going through the creation of the Temp table:

Map_RULE_DATE:
Mapping LOAD %RULE_ID, CREATED_DATE

From [..\..\External Data\Rule CREATED DATE.xlsx] (ooxml, embedded labels, table is Sheet1);

Regards,

Philippe

Re: mapping load and applymap()

Thu, 20 Mar 2014 16:03:41 GMT

If i directly load from the excel file then i'm seeing some weired values like 41709 etc not the date..I applied Date() though.

Re: mapping load and applymap()

maxgro — Thu, 20 Mar 2014 16:05:16 GMT

temp_RULE_DATE:
LOAD %RULE_ID,
CREATED_DATE
FROM
[..\..\External Data\Rule CREATED DATE.xlsx]
(ooxml, embedded labels, table is Sheet1);

Map_RULE_DATE:
Mapping
LOAD %RULE_ID,
CREATED_DATE
Resident temp_RULE_DATE;

Tbl:
load %RULE_ID,
     PRODUCT_NAME,
     PRODUCT_CATEGORY,
     CITY,
     STATE,                               // comma missing in your script
ApplyMap('Map_RULE_DATE',%RULE_ID,'Unknown')as Created_Date

from [..QVD\Tbl.qvd](qvd);

Re: mapping load and applymap()

Thu, 20 Mar 2014 16:09:22 GMT

Comma i missed while copying but it's fine in the script.it's the same i posted earlier though.

It's working fine for me but i can see anly one date for one %RULE_ID.Not sure if anything wrong in my script.

Though it's working fine.

Re: mapping load and applymap()

Thu, 20 Mar 2014 16:11:09 GMT

i'm not getting any error in the script.It's fethcing only one value.That's my concern.I think my script is correct.If not plz mention it.

Re: mapping load and applymap()

maxgro — Thu, 20 Mar 2014 16:21:59 GMT

the script is correct, I checked with my data

do missing dates exist in xls file (using %RULE_ID to join)?

could you post your files?

Re: mapping load and applymap()

Thu, 20 Mar 2014 16:23:05 GMT

temp_RULE_DATE:
LOAD %RULE_ID,
CREATED_DATE
FROM
[..\..\External Data\Rule CREATED DATE.xlsx]
(ooxml, embedded labels, table is Sheet1);

Map_RULE_DATE:
Mapping
LOAD %RULE_ID,
CREATED_DATE
Resident temp_RULE_DATE;

Drop tables temp_RULE_DATE;

Tbl:
load %RULE_ID,
     PRODUCT_NAME,
     PRODUCT_CATEGORY,
     CITY,
     STATE
ApplyMap('Map_RULE_DATE',%RULE_ID,null())as Created_Date

from [..QVD\Tbl.qvd](qvd);

any suggestion plz??? iT's working fine too.

Re: mapping load and applymap()

preminqlik — Thu, 20 Mar 2014 16:58:11 GMT

seems to be your requirement is one to Many relation ship ...applymap works for one to one ......your result is correct according to your applymap ..since for One %RULE_ID it gives one creation date .....

Re: mapping load and applymap()

alex_millan — Thu, 20 Mar 2014 17:07:27 GMT

As Philippe advised you, I would load directly from your Excel and format the date:

Date(DATE_FIELD, 'MM/DD/YYYY')

That would avoid your problems with 41709, etc...

Cheers!