Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transforming data during a load

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?

14 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

http://robwunderlich.com

Not applicable
Author

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?

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

swuehl
MVP
MVP

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!