7 Replies Latest reply: May 22, 2012 7:44 AM by Stefan Wühl RSS

    Why are null values appearing when I do a crosstable load?

    Joe Kirwan

      I load a simple table as follows:

       

      CustIDName       DayVal
      A100ANO100
      B234BNO150
      Z999XNO130

       

      Then I do a crosstable load to load following:

       

      CustID           Mon             Tue            Wed            Thu                Fri
      A1005075
      B234200200
      Z9994580

       

      My Script

       

      CrossTable(Day, DayLimit, 1)LOAD CustID,
          
      Mon,
          
      Tue,
          
      Wed,
          
      Thu,
          
      Fri

       

      FROM etc

       

      where Exists(StoreID)

      What I want is:

       

       

      CustIDNameDayValDayLimit
      A100ANO100125
      B234BNO150400
      Z999XNO130125

      What I get is:

      CustIDNameDayValDayLimit
      A100ANO100
      -125
      B234BNO150
      -400
      Z999XNO130
      -125

      I assumed that the CustdID and Name have been associated.

      Can someone advise what I am doing wrong?

       

      (When I do a simple load of the second table, just CustID and Mon, I do not encouner the problem.

      Joe

       

       

       

       

        • Why are null values appearing when I do a crosstable load?
          Stefan Wühl

          I can't reproduce your problem here. I assume you are creating a chart table in the front end, right?

           

          What are your dimensions and expressions?

           

          Could you upload a small sample QV file?

            • Re: Why are null values appearing when I do a crosstable load?
              Joe Kirwan

              Hi

               

              Files attached.

               

              Rgds

               

              Joe

               

               

              PS - I am a Personal Edition User, so will not be able to read any qv files you sent back to me

                • Re: Why are null values appearing when I do a crosstable load?
                  Stefan Wühl

                  Ok, two things I noticed:

                   

                  In your last load, you write:

                   

                  ...

                  WeekDay(DDD) as Day

                  ...

                   

                  Weekday() function takes a date as argument, while your DDD is a string, right?

                  Try

                   

                  dual(DDD,Match(DDD,'Mon','Tue','Wed','Thu','Fri','Sat','Sun')-1) as Day

                   

                  instead, to create field values that should match the field values created by Weekday() in the other table.

                   

                  Then, your fact table PayList seems to miss some data for some stores on some weekdays.

                  That's why you get the missing value '-' in the second dimension. Your can calculate a limit for the store and day, but this data is not linked to the PayList table containing the customer name information.

                   

                  [edit: You can enable the supress when value is Null for the second dimension, if this matches your requirements]

                   

                  Finally, I am not sure if your expressions do what they are supposed to do. For example, your limits are given per store and weekday, but your dailyval is given per date. So if you select a weekday, but you don't select a single week, your summed up daily values will probably exceed your daily limit (I assume this limit is per weekday and week). You see what I mean? I guess that's why your DayExcess column shows all red.

                   

                  I hope this gets you a step further,

                  Stefan

                    • Re: Why are null values appearing when I do a crosstable load?
                      Joe Kirwan

                      Hi Stefan

                       

                      Your proposed solutions seem to work. I just don't udnerstand why? Maybe you can help.

                       

                      It seems that if I simply enable the Null Suppress, this seems to overcome the Null CustName.

                       

                      I don't understand how the null CustNAme happens. I may have a fundamental misunderstanding about how Qlikview works.

                       

                      I load the StoreID and then LEFT JOIN the CustName in my table PayList. PayList does not necessarily have a record for each StoreID for each day.

                       

                      I then load StoreID and Daily Limit in CustMast

                       

                      Now, if I build a chart with StoreID and Daily Limit, I thought that I could introduce the CustName based on the associations on StoreID. So where does the null come from?

                       

                      DUAL function - In general, I'm not 100% on which date formats I am using.but I get your point that I am confusing date as string and date as argument. Your DUAL function ( which I need to study) seems to unify DDD to agree with the other format of Mon, Tue, Wed etc

                       

                      Expressions (These are all designed when a DATE is selected)

                       

                      Day Val - This is simply the total VAL for the selected date fields. For a valid computation, a DATE needs to be accepted. I think my expression is correct.

                       

                      Day Limit - This should calculate the Day Limit for the DAY assoicated with the selected DATE. I need a Set Analysis formula for this.

                       

                      Cum Week Val - This should calculate the Val for the WEEK associated with the selected DATE. The following formula seems to work

                      sum({$<Day = >} Val)

                       

                      Week Limit - This shoudl always total the DayLimits for all DAY. I think I have it working with

                      sum({$<Day = >} DayLimit)

                       

                      (EDIT: This formula does not work, as it does not calculate for all StoreID, only those with activity on the selected DATE.)

                       

                      though I'm not quite sure what I am doing with the Set Analysis part of this or the previous expression

                        • Re: Why are null values appearing when I do a crosstable load?
                          Stefan Wühl

                          >I don't understand how the null CustNAme happens. I may have a fundamental misunderstanding about how Qlikview works.

                           

                          I tried to explain this in my previous post, sorry if this was not clear enough.

                          In one table you have data per Store and CustomerName, in the other only per Store.

                          So if you create an expression that uses data from the first table (daily Vals), and another that uses data from the second (Limits), you will get results that can be goruped by Store and CustomerName for the first expression but only can grouped by Store for the second expression. If you put the second in the same chart as the first expression and use two dimensions Store and CustomerName (group by Store and CustomerName), QV will try to retrieve the CustomerName for the grouping of the seconds expression by folling the link to the other table using Store and Day. But for some Days, there just is no complete link, right? That's why QV uses a separate line with a NULL / missing value sign '-' to show these Day's Limit expression values.

                           

                          Not sure if this explains it any better..

                           

                          Back to your problem, I think you can avoid the issue with the additional row even better by just using one table instead of the two:

                           

                          PayList:

                          LOAD Date,

                               Week(Date) as Week,

                               Month(Date) as Month,

                               WeekDay(Date) as Day,

                               StoreID,

                               Val

                          FROM

                          CrossNull.xls

                          (biff, embedded labels, table is DayPayList$);

                           

                          TempCustMast:

                          CrossTable(DDD, DayLimit,3)

                          LOAD StoreID, CustName, CustGrp,

                               Mon,

                               Tue,

                               Wed,

                               Thu,

                               Fri     

                          FROM

                          CrossNull.xls

                          (biff, embedded labels, table is CustMaster$);

                           

                          LEFT JOIN (PayList)

                           

                          LOAD

                              StoreID, CustName, CustGrp, DayLimit,

                              dual(DDD,Match(DDD,'Mon','Tue','Wed','Thu','Fri','Sat','Sun')-1) as Day

                          Resident TempCustMast;

                           

                          drop table TempCustMast;

                           

                          Then for your last two, cumulative expressions, try

                           

                          =sum({<Date=, Week=p()>} Val)

                           

                          resp.

                           

                          =sum({<Date=, Week=p()>} DayLimit)

                           

                          This disregards your selections in Date field, but selects the possible Week(s) for the chosen Dates.

                           

                          Hope this helps,

                          Stefan

                            • Why are null values appearing when I do a crosstable load?
                              Joe Kirwan

                              Hi Stefan

                               

                              Thank you for expanding on your explanation of my null problem

                               

                              Quote: "So if you create an expression that uses data from the first table (daily Vals), and another that uses data from the second (Limits), you will get results that can be goruped by Store and CustomerName for the first expression but only can grouped by Store for the second expression"

                               

                              I reckon I did have a fundamental misunderstanding of how Qlikview associations work. I thought that once I loaded in a table with Store and Customer, then any instance of Store ID as a dimension cold be associated with the Customer name IRRESPECTIVE OF THE EXPRESSION.

                               

                              It seems this is not the case.

                               

                              I also note your recommendation regarding the creation of a single table. I can confirm this approach works, as I used it myself last night!

                               

                              So maybe there is hope for me.

                               

                              Ta

                               

                              Joe

                                • Why are null values appearing when I do a crosstable load?
                                  Stefan Wühl

                                  There is always enough hope for everyone!

                                   

                                  Honestly, learning QV's associative DB is sometimes difficult or different from other concepts, but I am sure you will master it soon.

                                   

                                  >I thought that once I loaded in a table with Store and Customer, then any instance of Store ID as a dimension cold be associated with the Customer name IRRESPECTIVE OF THE EXPRESSION.

                                   

                                  If you've only used Store as link between your tables, that would be true, but you are linking by Store and Day!