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

[mazacini]

I load a simple table as follows:

CustID	Name	DayVal
A100	ANO	100
B234	BNO	150
Z999	XNO	130

Then I do a crosstable load to load following:

CustID	Mon	Tue	Wed	Thu	Fri
A100	50		75
B234		200			200
Z999	45			80

My Script

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

FROM etc

where Exists(StoreID)

What I want is:

CustID	Name	DayVal	DayLimit
A100	ANO	100	125
B234	BNO	150	400
Z999	XNO	130	125

What I get is:

CustID	Name	DayVal	DayLimit
A100	ANO	100
	-		125
B234	BNO	150
	-		400
Z999	XNO	130
	-		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

[swuehl]

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?

[mazacini]

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

[swuehl]

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

[mazacini]

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

[swuehl · Accepted solution]

>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

[mazacini]

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

[swuehl]

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!