Transforming data during a load

[#1]

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?

[#2]

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

[swuehl]

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

[#4]

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.

[swuehl]

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;

[#6]

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

RR

[#7]

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

[#8]

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;

[swuehl]

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

[#10]

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.

RR