Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
have you tried just to transform your txt in to qvd files?
Regards
André Gomes
Hi, yes sorry that piece of script is just a sample but in the main script i transform them into QVD's
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.
That sounds good but forgive me for been dumb, how would i write that up in the script???
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;
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);
This blog by HIC compares Join vs. ApplyMap.
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.
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