topic Re: Transforming data during a load in QlikView

Transforming data during a load

Tue, 02 Aug 2011 08:45:07 GMT

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?

Transforming data during a load

Tue, 02 Aug 2011 12:20:40 GMT

Hello Mark,

I think your problem can be solved easliy using a concatenate load. This means missing fields will be automatically created and get the nullval. Even if it sounds very simple it is worth a try. Test my little example:

ReportA:
LOAD * Inline [
Product,Month, Year,       Charge A
A,             01,           2010,       100
A,             02,           2010,       110
A,             03,           2010,       120
A,             04,           2010,       130
];
Concatenate // this looks strange for beginners, but may be you will enjoy the result
Load * inline [
Product,Month, Year,       Charge B
B,             01,           2010,       50
B,             02,           2010,       45
B,             03,           2010,       40
B,             04,           2010,       35
];

Regards, Roland

Transforming data during a load

swuehl — Tue, 02 Aug 2011 13:10:43 GMT

Hi Roland,

I think Mark's problem is that he doesn't want Null in the specific fields, he wants a zero value.

I tried

NULLASVALUE "Charge*";

Set NullValue ='0';

statements before the concatenation, but surprisingly that only affected Charge B values (which were correctly set to zero, but not the other Charges).

Regards,

Stefan

Transforming data during a load

Tue, 02 Aug 2011 13:16:00 GMT

Stefan is right - my problem is replacing Null values with zeroes or finding another way to create those extra fields with zeroes in.

In practise the standardised data set will have perhaps 30-odd columns (I didn't include them all above to save space), and my reports may contain perhaps any 20 of these, of which the only common onese between any reports will be Calendar_Mth and Calendar_Yr.

Transforming data during a load

swuehl — Tue, 02 Aug 2011 13:24:02 GMT

Hi Mark,

one solution might be to first concatenate like Roland suggested and then do a reload for the final data table, replacing Nulls with zeros, like

Finaltable:

LOAD Product

, Month

, Year

if(isNull([Charge A]=-1, 0, [Charge A]) as [Charge A]

....

resident Reporttable;

Transforming data during a load

Tue, 02 Aug 2011 15:20:45 GMT

Hello again,

there are two topics:

- creating new fields and

- insert '0' as a default value.

a) I would prefer to set them to null, because null means "no value" (empty) instead of 0; 0 represents zero.

b) if you prefer the zero, I agree with Stefans "Resident Load" after all concatenate loads are successfullly done

Re: Transforming data during a load

Tue, 02 Aug 2011 15:54:31 GMT

Hi Stefan,

may be surprising or may be not. (In fact I was surprised too 😉 )

So take a look at my example app: Existing fields coming in with concat loads are filled correctly because they did exist before this particular load. Or with other words: nulls created prior to a concat load are not refilled (updated). I suppose this is a feature, not a bug and can be "solved" as you still described above.

Regards, Roland

Transforming data during a load

Tue, 02 Aug 2011 16:28:18 GMT

Is this what you need?

ReportA:
LOAD * Inline [
Product,Month, Year,       Charge A
A,             01,           2010,       100
A,             02,           2010,       110
A,             03,           2010,       120
A,             04,           2010,       130
];
Concatenate // this looks strange for beginners, but may be you will enjoy the result
Load * inline [
Product,Month, Year,       Charge B
B,             01,           2010,       50
B,             02,           2010,       45
B,             03,           2010,       40
B,             04,           2010,       35
];

NULLASVALUE "Charge*";
Set NullValue ='0';
Data:
Load *, 1 as id
resident ReportA;
drop table ReportA;
drop field id;

Transforming data during a load

swuehl — Tue, 02 Aug 2011 17:26:51 GMT

Hi thefourth,

looks good to me..

Roland, what I don't understand at the moment: Why should the nulls for Charge B already have been created in the first table before the concatenation? That sounds a bit like supervision to me.

If they are created at the same time with the nulls for Charge A, why is the behaviour different (since we stated a nullasvalue for both fields before)?

All, have a nice evening..

Stefan

Re: Transforming data during a load

Tue, 02 Aug 2011 18:44:02 GMT

Hi thefourth,

thank you, this is what Stefan said in his post above and should work for mark as expected.

Hi Stefan,

Why should the nulls for Charge B already have been created in the first table before the concatenation?

-->

They are not created as nulls are never been created at all. Null means empty or not availible. So the first four rows of my example are created without a field "ChargeB". And afterwards you are concatenating rows with a new field called ChargeB. This second load will not refill or update any existing rows. Take a look at my exam again, loading ChargeC or ChargeD. Got it ?

If not don't hesitate to ask for more details.

Re: Transforming data during a load

rwunderlich — Tue, 02 Aug 2011 23:39:37 GMT

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

Re: Transforming data during a load

Wed, 03 Aug 2011 07:55:04 GMT

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?

Re: Transforming data during a load

Wed, 03 Aug 2011 11:55:07 GMT

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

Re: Transforming data during a load

rwunderlich — Wed, 03 Aug 2011 16:06:56 GMT

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

Re: Transforming data during a load

swuehl — Wed, 03 Aug 2011 18:20:52 GMT

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!