Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guys,
is it possible to have fields as dimensions of a bar chart?
for e.g. i have sales, cost, netrevenue and location of table1
the dimensions would be field1, field2 and field3 while the expression would be the locationfield
so i can compare values of field1, field2 and field3 easily since their bars are side by side
location1 location2 location3 location1 location2 location3 location1 location2 location3
sales cost netrevenue
location1, location2 and location3 are values of the field location
i know that it is simple to have sales, cost and netrevenue as the expression with location as the dimension but is it possible in the scenario i have described above?
Hi,
The code above is just an example on how to get a crosstable load working and the results it will return. I don't know your database, but it should work anyway tweaking the script a little.
But for clarification sake, the crosstable above will return a table with three columns, one with Location, the other with Item (where possible values are "Sales", "Net" and "Cost") and the other one Value, storing all the amounts in the script, very similar to what Vicky describes above.
Again, it's just an idea. If that doesn't fit your requirements or is more complex than the other solution, forget about it. I'll attach a very simple example application tomorrow morning for your perusal, just in case.
Hope that helps.
BI Consultant
i think i understand vicky suggestion for my initial objective
what if now,
how could i modify the script further, or do i even need another script now?
PS: i already have a date field that works properly in previous charts to show trends in weeks
how do you use the WHERE condition with the field name?
if i have a field name SALES im supposed to say WHERE field=SALES?
i tried that and it tells me field not found,
Field not found - <field>
UTILIZATION_WEEKLY2:
LOAD
Site,
START_DATETIME,
`90-000` as Value,
'PMFG' as Type,
TOTAL_TIME
RESIDENT tUTILIZATION_WEEKLY
WHERE field = `90-000`
this is my original table,
tUTILIZATION_WEEKLY:
LOAD `START_DATETIME`,
`10-000`,
`20-000`,
`30-000`,
`40-000`,
`50-000`,
`60-000`,
`70-000`,
`80-000`,
`90-000`,
`A0-000`,
`B0-000`,
`C0-000`,
`D0-000`,
`E0-000`,
`F0-000`,
`TOTAL_TIME`,
`PRODUCTIVE_MFG_LOSS`,
'A' as Site;
SQL SELECT *
FROM A.`UTILIZATION_WEEKLY`;
this is the script i used,
UTILIZATION_WEEKLY2:
LOAD
Site,
START_DATETIME,
`90-000` as Value,
'PMFG' as Type,
TOTAL_TIME
RESIDENT tUTILIZATION_WEEKLY
WHERE field = `90-000`;
JOIN LOAD
Site,
START_DATETIME,
PRODUCTIVE_MFG_LOSS as Value,
'SITELOSS' as Type,
TOTAL_TIME
RESIDENT tUTILIZATION_WEEKLY
WHERE field = PRODUCTIVE_MFG_LOSS;
JOIN LOAD
etc...
In your load script you would do WHERE SALES = 'XXX'.
It sounds more like you are working on a chart though? In that case you would want to implement either some IF logic or Set Analysis should you need to limit the calculation to a subset of 'Sales'
In a chart you should just need to set your dimension to the same date field thats in your other chart & then add sum(sales), sum([net revenue]), etc... in a stacked bar chart.
Post a sample of your dataset & description of what you are trying to accomplish & we can likely clarify better what you need do.
no im not trying to do a conditional load like that
im modifying from what vicky has suggested above...
for the chart it's not jus a single bar on each week but multiple bars depending on the number locations i've selected
so if i selected 3 locations i have have 3 bars for each of the week and each bar has is stacked with the sales/costs/netrevenue
Good Morning,
yes,
Field not found - <field>
is right, because you haven't got this name in your table. Are '90-000' ect. your fieldnames? Then you have write
WHERE '90-000' = value (always WHERE field name = field value).
Value is, what you see, if you load your data in a table.
For example: field name = Sales, field values = 200,144,155,188,24,265
And now is the condition: WHERE Sales > 150
Then you load all data with 155,188,200,265
Clear?
Regards vicky
i understand how the mathematical operators work in a WHERE condition but now how you directly use the fieldname
yes 90-000 etc are my fieldnames...
values under x0-000 (where x is from 1-9 and A-F) are just integers which i will use to calculate stuffs
WHERE field = '90-000' does not work...
Hi,
According to your script above, "90-000" is not the field name, but the value. The field name is "Value". So the script should look like
UTILIZATION_WEEKLY2:
LOAD
Site,
START_DATETIME,
'90-000' as Value,
'PMFG' as Type,
TOTAL_TIME
RESIDENT tUTILIZATION_WEEKLY
WHERE Value = '90-000';
Both values and field names are case sensitive, so "Value" and "value" can be different fields in the same table.
Hope that helps.
BI Consultant
oh i have to follow the "'90-000' as Value" i typed above?
i thought the WHERE field = '90-000' was talking about the resident table instead of this new table...
Hi,
The script above loads from the resident table tUTILIZATION_WEEKLY those records where field "Value" are equal to "90-000". Anyway, it souns very confusing to me. According to your original script, the load should be as follows
tUTILIZATION_WEEKLY:
LOAD `START_DATETIME`, // all these are fieldnames coming from the A.UTILIZATION_PWEEKLY table in your database
`10-000`,
`20-000`,
`30-000`,
`40-000`,
`50-000`,
`60-000`,
`70-000`,
`80-000`,
`90-000`,
`A0-000`,
`B0-000`,
`C0-000`,
`D0-000`,
`E0-000`,
`F0-000`,
`TOTAL_TIME`,
`PRODUCTIVE_MFG_LOSS`,
'A' as Site;
SQL SELECT *
FROM A.`UTILIZATION_WEEKLY`;
ResidentLoadTable1:
LOAD Site, // this field will always have value "A"
START_DATETIME, // this field exists in the resident table
"90-000" AS Value, // this field exists in the resident table,
// I always use "" or [] to mark field names in QlikView when they have special chars
'PMFG' as Type, // this is a new field you are creating in this table
TOTAL_TIME // this field exists in the resident table
RESIDENT tUTILIZATION_WEEKLY;
You don't need the WHERE in this case. This table loads, based on the previous one, only the values in field "90-000" (now called "Value" in this new table) as "PMFG" as Type. You are already using this new "Type" field, so you don't need to specify which data you want to get from the resident load.
The following one should be
JOIN LOAD
Site,
START_DATETIME,
PRODUCTIVE_MFG_LOSS as Value,
'SITELOSS' as Type,
TOTAL_TIME
RESIDENT tUTILIZATION_WEEKLY;
In this case, Value is filled with values from original field "PRODUCTIVE_MFG_LOSS", and stored and loaded into the "Value" field. Again, there's no need to use the WHERE clause.
Hope that helps to understand how this works.
BI Consultant
P.S.: I'm still thinking of using the crosstable load here, now that I know your main table, because it makes a lot of sense to me, but nevermind.