topic Re: Reducing load size of script in QlikView

Reducing load size of script

Wed, 28 Jan 2015 16:03:20 GMT

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.

Re: Reducing load size of script

agomes1971 — Wed, 28 Jan 2015 16:06:29 GMT

Hi,

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

Regards

André Gomes

Re: Reducing load size of script

Wed, 28 Jan 2015 16:09:59 GMT

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

Re: Reducing load size of script

Anonymous — Wed, 28 Jan 2015 16:19:42 GMT

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.

Re: Reducing load size of script

Wed, 28 Jan 2015 16:37:07 GMT

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

Re: Reducing load size of script

ChiragPradhan — Wed, 28 Jan 2015 16:50:56 GMT

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;

Re: Reducing load size of script

Anonymous — Wed, 28 Jan 2015 16:52:23 GMT

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

Re: Reducing load size of script

Anonymous — Wed, 28 Jan 2015 16:54:38 GMT

This blog by HIC compares Join vs. ApplyMap.

Don't join - use Applymap instead

Re: Reducing load size of script

ChiragPradhan — Wed, 28 Jan 2015 17:08:04 GMT

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.

Re: Reducing load size of script

rwunderlich — Wed, 28 Jan 2015 17:08:53 GMT

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

Re: Reducing load size of script

Thu, 29 Jan 2015 11:18:38 GMT

Hi, Both good answers and both seem to run in my script and reduce the size. However they are bringing me back the correct data. I dont know why but when i test it im getting the wrong amounts.

To explain which way i need it a bit better below is an example of what way i would need my final table.

File 1 claim ID is 12345 and it shows all the description info and financials which tell me what each ID total incurred was at the start of every year.

Now in File 2 claim ID 12345 may be in this file multiple times as it show payments on that ID over a four year period so as below

Claim ID Fin Mth Fin Yr Fin Amt

12345 2 2011 £250

12345 5 2011 £350

12345 9 2011 £150

12345 7 2012 £450

12345 3 2013 £550

12345 1 2014 £250

So my plan would be to use file 1 to identify Claim ID (12345) then to tell me what the incurred was at the start of 2011

I then use file 2's payment info to look at the running total over 12 months.

So Eg Incurred at start of 2011 is £100

Yr Inc Mth 1 2 3 4 5 6 7 8 9 10 11 12

2011 £100 £100 £350 £350 £350 £700 £700 £700 £700 £850 £850 £850 £850

The way i have this previously set up and working is.

I set up variables in the script which look into file 1 and identify the start of year figures .

Then when i get to my graph i divide the sum of the FinAmt from file 2 by the variable(start of year figures) from file 1.

I then plot this by month on the graph which shows me the run off of a particular years. Each year has its own line.

No that you know the back round you might be help.

So the problem now is if I use apply map or join they dont seem to match up...

Thanks

Re: Reducing load size of script

rwunderlich — Thu, 29 Jan 2015 14:48:26 GMT

I don't see enough of the data to be sure, but it sounds like it may be difficult to join and get the correct results. Have you considered using leaving as two tables and using autonumber() on the ClaimID field to reduce it's size?

-Rob