Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a query about loading data - any help would be very appreicated!
I'm trying to create a QlikView document to store and manage my firm's reporting data. I have several reports produced by the same program (Prophet) in .prn format which I want to QlikView to standardise before saving as .qvds for later use in various QlikView documents. Different reports contain different fields and if a field is missing from a particular report then I want QlikView to create the field and just populate it with zeroes. My logic for performing this standardisation step is that it will allow various new QlikView documents to easily aggregate and manipulate the data from different reports which would otherwise look quite different.
As an example, one report will contain a column for ‘Charge A’ with values populated, but no column for ‘Charge B’, whilst another report would contain values for 'Charge B' but nothing for 'Charge A'. I want to create a group of .qvds for these reports which have identical fields; i.e. these prns:
Report A.prn
Product Month Year Charge A
A 01 2010 100
A 02 2010 110
A 03 2010 120
A 04 2010 130
… … … …
Report B.prn
Product Month Year Charge B
B 01 2010 50
B 02 2010 45
B 03 2010 40
B 04 2010 35
… … … …
Would be loaded in by QlikView and transformed before being saved as .qvds which would look as follows:
Report A.qvd
Product Month Year Charge A Charge B
A 01 2010 100 0
A 02 2010 110 0
A 03 2010 120 0
A 04 2010 130 0
… … … … …
Report B.qvd
Product Month Year Charge A Charge B
B 01 2010 0 50
B 02 2010 0 45
B 03 2010 0 40
B 04 2010 0 35
… … … … …
My problem is that I’m not really sure how to do this! My latest attempt is to create a ‘dummy’ table just containing zeroes for all the possible fields before joining it with the actual data I want to standardise. My hope was that if the field isn’t present in the .prn report the joined table would keep the column of zeroes from the original dummy table. However it doesn’t – it keeps the field but replaces the zeroes with a ‘-‘.
PolicyData:
LOAD '$(varProduct)' as Product,
CALENDAR_MTH,
CALENDAR_YR,
0 as DEATH_OUTGO,
0 as EXP_RELIEVBL,
0 as GROSS_INVRET,
0 as GROSS_PROFIT,
0 as INC_MATH_RES,
0 as INC_SOL_MARG,
0 as INC_UNIT_RES,
0 as INIT_COMM,
0 as LF_I_E_TAX,
0 as LF_PROF_TAX,
0 as LIFEFUND_TAX,
0 as MAT_OUTGO,
0 as MATH_RES_IF,
0 as NET_PROFIT,
0 as NO_DEATHS,
0 as NO_MATS,
0 as NO_POLS_IF,
0 as NO_PUPS_IF,
0 as NO_SURRS,
0 as tot_deal_chg,
0 as tot_ec1_chg,
0 as tot_ec2_chg,
0 as tot_mc1_chg,
0 as tot_mc2_chg,
FROM
$(varFilePath)\$(varFileName).prn;
Right Join (PolicyData)
LOAD '$(varProduct)' as Product,
*
FROM
$(varFilePath)\$(varFileName).prn;
Store PolicyData into
Does anyone know how I might achieve the transfomation of the data that I want?
Hi Roland,
You were real close to a single pass solution. Just need a load (with no data) of the variant fields.
NullAsValue 'Charge*' ;
set NullValue=0;
Report:
LOAD * INLINE [
ChargeA, ChargeB, ChargeC
]
;
And then concatenate the PRN files to that. No additional resident load needed. See attached.
-Rob
Thanks everyone, I have managed to get a solution now!
The various columns I had with null values aren't all of the name 'Charge*', so I altered Rob's code to read:
NullAsValue '*' ;
set NullValue=0;
PolicyData:
LOAD * INLINE [
RunDate,Company,Product,CALENDAR_MTH,CALENDAR_YR,DEATH_OUTGO,EXP_RELIEVBL,GROSS_INVRET,GROSS_PROFIT,INC_MATH_RES,INC_SOL_MARG,INC_UNIT_RES,INIT_COMM,LF_I_E_TAX,LF_PROF_TAX,LIFEFUND_TAX,MAT_OUTGO,MATH_RES_IF,NET_PROFIT,NO_DEATHS,NO_MATS,NO_POLS_IF,NO_PUPS_IF,NO_SURRS,O_FII,O_RCG_CHG,O_RCG_UNCHG,O_TAX,O_TAXBL_INC,O_UFII,O_UNRCG,PARTSV_OUTGO,PREM_INC,REN_COMM,REN_EXP,REN_FUND_CHG,rep_surr_pen,RIDERC_OUTGO,SOLV_MARG_IF,START_PVFP_A,STER_RES_IF,SURR_OUTGO,TOT_COMM,tot_deal_chg,tot_ec1_chg,tot_ec2_chg,TOT_EXP,tot_inifxd_exp,tot_init_chg,tot_lty_chg,tot_mc1_chg,tot_mc2_chg
]
;
Concatenate
LOAD '$(varRunDate)' as RunDate,
'$(varCompany)' as Company,
'$(varProduct)' as Product
*
FROM
$(varFilePath)\$(varFileName).prn
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines, filters(
Remove(Row, Pos(Top, 2))
));
Do you see any potential problems with using " NullAsValue '*' " - i.e. using just the asterisk rather than using a string-and-asterisk?
Hi Rob,
nice to hear from you. And what a solution! This is the difference . . . 🙂
I'd like to give you some additional points.
It's true I was pretty close but it is also true I was far away from it.
Regards, Roland
I would be inclined to use NullAsValue only on the value fields, not fields like Company. If Company is missing from the data I would want a null instead of a "0". I would also only list the value fields I want defaults for in the initial load. I don't know your app, but for example I would not want Product in the default list.
-Rob
Mark,
if your script continues with loading more tables and fields for which you want back the standard behaviour, you should / might consider switching back to standard Null using
NullAsNull *;
statement.
Roland, I think I got your point which gives a possible reason to explain the symptom. My Point of view is, if I state NullAsValue for all fields first in the script (as detailed above), I won't expect to find a NULL afterwards anywhere in my data table.
That's how I interprete the manual (which I found weak on that point) and what is my expectation as a user.
I personally think that having still NULLs is therefore a defect rather than a feature. A defect with an easy workaround, but still a defect.
Just my 0.02 €.
All, have a nice evening!