Mapping Multiple Fieds

[#1]

Hi,

I am trying to map several fields from one table to another.

I am building by Miguel Roone example. (Mapping Multiple Fieds with only one Mapping Load )

But it is not working ;/

I getting the error:

I’am using this script:

Leads_Mapping:
Mapping load unique_num,
unique_num_Customer    
FROM

(qvd);



ApplicantCustomer:
Mapping load Leads_Unique_Num,
ApplicantCustomerData
FROM

(qvd);


tableA:
load Date_Day,
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',1) as age,
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',2) as [Marital Status],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',3) as[Num Of Children],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',4) as[Education],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',5) as[Occupation],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',6) as[First Name],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',7) as[Last Name],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',8) as[Email],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',9) as[Gender],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',10) as[Birth Date],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',11) as[City],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',12) as[Street],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',13) as[Residence Country],
SubField(ApplyMap('ApplicantCustomer',Leads_Unique_Num),'|',14) as[Paid as LeadsData]

;
LOAD Date_Day,
If( match(ActionType_ID,5,6,7,8)>0 and Department = 2,ApplyMap('Leads_Mapping',Unique_Num),Unique_Num) as Leads_Unique_Num    
from (qvd);

I do not know what to do,And I rather not use join

[Lech_Miszkiewicz]

Hi

is this all script you have?

Script error refers to part of script which you have not included in here which is a table "Media_log_summary". I guess you have a mistake in further or earlier part of the script, arent you?

If you could attach whole script it would be easier to investigate it.

regards

Lech

[mkelemen · Accepted solution]

Hi,

you are missing a space between as and [. It can not be interpreted and therefore you get the "Missing From" error.

Change to "as [" and you should be good to go.

Btw. this script uses ApplyMap a lot of times.

I would do the applymap just once and then use subfield on the result:

tableA:

load

Date_Day,

SubField(AppliedMap,'|',1) as age,

SubField(AppliedMap,'|',2) as [Marital Status],

SubField(AppliedMap,'|',3) as [Num Of Children],

SubField(AppliedMap,'|',4) as [Education],

SubField(AppliedMap,'|',5) as [Occupation],

SubField(AppliedMap,'|',6) as [First Name],

SubField(AppliedMap,'|',7) as [Last Name],

SubField(AppliedMap,'|',8) as [Email],

SubField(AppliedMap,'|',9) as [Gender],

SubField(AppliedMap,'|',10) as [Birth Date],

SubField(AppliedMap,'|',11) as [City],

SubField(AppliedMap,'|',12) as [Street],

SubField(AppliedMap,'|',13) as [Residence Country],

SubField(AppliedMap,'|',14) as [Paid as LeadsData]

;

Load

Date_Day,

ApplyMap('ApplicantCustomer',Leads_Unique_Num) as AppliedMap

;

LOAD Date_Day,

If( match(ActionType_ID,5,6,7,8)>0 and Department = 2,ApplyMap('Leads_Mapping',Unique_Num),Unique_Num) as Leads_Unique_Num    

from (qvd);

or this to replace the last two loads to minimize number of preceding loads:

LOAD

Date_Day,

If( match(ActionType_ID,5,6,7,8)>0 and Department = 2, ApplyMap('ApplicantCustomer',ApplyMap('Leads_Mapping',Unique_Num),Unique_Num)) as AppliedMap

from (qvd);

BR,

  Matus

[#4]

So simple! thank you Matus

As you recommended,I tried to replace the last two loads with:

If( match(ActionType_ID,5,6,7,8)>0 and Department = 2,ApplyMap('ApplicantCustomer',ApplyMap('Leads_Mapping',Unique_Num)),ApplyMap('ApplicantCustomer',Unique_Num)) as AppliedMap,

But I'm not getting any result

I will try to explain what I am trying to do:

I am trying to implement an incremental load from existing qvd file, And append new data to a new QVD file.

After appending with new raw data and save in new qvd,  I will  need to perform two left join:

1. With a table that contains 20 million records, And the information in the table is Permanent

Example of columns: Last Name, Date of birth.

1. With a table that contains 20 million records, And the information in the table is Variable

Example of columns: Status.

To deal with the first left join I used multiple mapping fieds with the first table.

the Performance is slow and I'm not sure this is the right way to build such a script

Sample script attached.

[mkelemen]

Hi,

you are storing Media_Log_Summary into QVD2 but I do not see the table anywhere. This should throw an error if the table does not exist. I suppose in the qvw is just a part of the script.

Anyway - it seems strange that you do all the stuff with Fact_table and then you drop it and store Media_Log_Summary. Have a look at that.

BR,

  Matus

[#6]

Sorry, I meant storing "Fact_table" into qvd2

Attached is the correct script

[#7]

i will open a new discussion, Thanks Matus !