Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
>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
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?
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
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
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
>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
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
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!