14 Replies Latest reply: Aug 3, 2011 2:20 PM by Stefan Wühl RSS

    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 [Y:\Work\PolicyData_$(varProduct).qvd];

       

       

      Does anyone know how I might achieve the transfomation of the data that I want?

        • Transforming data during a load

          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
              Stefan Wühl

              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

                  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
                      Stefan Wühl

                      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

                        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

                      • Re: Transforming data during a load

                        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

                          • Re: Transforming data during a load
                            Rob Wunderlich

                            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

                                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
                                    Rob Wunderlich

                                    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
                                      Stefan Wühl

                                      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!

                                    • Re: Transforming data during a load

                                      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

                                  • Transforming data during a load

                                    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
                                        Stefan Wühl

                                        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

                                            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