mapping load and applymap()

[#1]

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.    

[MK_QSL]

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

[pgrenier]

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

[#4]

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.

[maxgro]

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);

[#6]

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.

[#7]

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.

[maxgro]

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?

[#9 · Accepted solution]

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. 

[preminqlik]

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 .....