Reducing load size of script

[#1]

Hi All

I have a problem which i hope you can shed some light on.

I also have attached an example of the source information i am trying to upload.

I have two files which I am loading into my script. They both relate to each other using a unique ID.

File 1 will has  unique ID codes and some informative information.

File 2 has all the payments relating to ID codes.

Now there are about 700,000 unique ID's in File 1 and about 2.5 million payments relating to those 700,000 in File 2.

I am linking the two on a the ID code however in my dashboard i don't use ID anywhere. I just use it to create the link so I can use other fields in both.

as you can imagine this make my load long and the size of my file large.

My question is can i create the link somehow and not have ID's brought into my load.

Feel free to ask more questions if i have not explained it well.

Here is a picture of my Table join as as i said attached is a sample of what the two files look like.

Here is my Script

LOAD ID,

     CM_Paid,

     CM_OS,

     CM_Rec,

     CM_NIC,

     PM_Paid,

     PM_OS,

     PM_Rec,

     PM_NIC,

     CM_PM_Movmnt,

     CM_PM_Resv_Movmnt,

     ReportMonth,

     PY1_ReserveMvmntUpToPY,

     PY1_PY_End_Incurred,

     PY2_ReserveMvmntUpToPY,

     PY2_PY_End_Incurred,

     PY3_ReserveMvmntUpToPY,

     PY3_PY_End_Incurred,

     PY4_ReserveMvmntUpToPY,

     PY4_PY_End_Incurred

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

LOAD ID, 

     FinYr,

     FinMth,

     FinAmt

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

Any help would be much appreciated.

[agomes1971]

Hi,

have you tried just to transform your txt in to qvd files?

Regards

André Gomes

[#3]

Hi, yes sorry that piece of script is just a sample but in the main script i transform them into QVD's

[Anonymous]

How about using the UK7RunOff_Dec14.txt file as the source for 3 Mapping Loads.

Then load UK7Claim_Dec14.txt with additionally 3 ApplyMap() 's using the Mapping Loads.

That way you will end up with a single table in your data model.

You may or may not need to adjust some of front end GUI objects depending on what they do.

[#5]

That sounds good but forgive me for been dumb, how would i write that up in the script???

[ChiragPradhan]

You could use a join and drop the ID field. That way you will have one table with all ID-Claim combination which performs faster and won't have to worry about the ID field. Something like this

LOAD ID,

     CM_Paid,

     CM_OS,

     CM_Rec,

     CM_NIC,

     PM_Paid,

     PM_OS,

     PM_Rec,

     PM_NIC,

     CM_PM_Movmnt,

     CM_PM_Resv_Movmnt,

     ReportMonth,

     PY1_ReserveMvmntUpToPY,

     PY1_PY_End_Incurred,

     PY2_ReserveMvmntUpToPY,

     PY2_PY_End_Incurred,

     PY3_ReserveMvmntUpToPY,

     PY3_PY_End_Incurred,

     PY4_ReserveMvmntUpToPY,

     PY4_PY_End_Incurred

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

JOIN

LOAD ID,

     FinYr,

     FinMth,

     FinAmt

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

DROP FIELD ID;

[Anonymous]

Try something like this.  I have done it freehand & with copy/paste so there may well be a typo.

MapFinYr:

Mapping Load

     ID,

     FinYr

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

MapFinMth:

Mapping Load

     ID,

     FinMth

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

MapFinAmt:

Mapping Load

     ID,

     FinAmt

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

SingleTable:

LOAD

     Applymap('MapFinYr' , ID ) as FinYr ,

     Applymap('MapFinMth' , ID ) as FinMth,

     Applymap('MapFinAmt', ID ) as FinAmt,

     CM_Paid,

     CM_OS,

     CM_Rec,

     CM_NIC,

     PM_Paid,

     PM_OS,

     PM_Rec,

     PM_NIC,

     CM_PM_Movmnt,

     CM_PM_Resv_Movmnt,

     ReportMonth,

     PY1_ReserveMvmntUpToPY,

     PY1_PY_End_Incurred,

     PY2_ReserveMvmntUpToPY,

     PY2_PY_End_Incurred,

     PY3_ReserveMvmntUpToPY,

     PY3_PY_End_Incurred,

     PY4_ReserveMvmntUpToPY,

     PY4_PY_End_Incurred

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq, no eof);

[Anonymous]

This blog by HIC compares Join vs. ApplyMap.

Don't join - use Applymap instead

[ChiragPradhan]

I am not sure if in this particular case, using apply map is completely justified as you will have the same Claim ID repeated for each month within a financial year.

[rwunderlich]

I would definitely use JOIN. ApplyMap is a lot of extra work in this case because of the multiple fields. I would recommend that be a LEFT JOIN.

-Rob