Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My data is in generic format.
I use the GENERIC keyword to load it into (columnar?) format.
My example is analogous to Henric's example here:
https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
So I will ask the questions in terms of the values he describes;
Height
Width
Length
Weight
Diameter
Color
In my case I want another calculated column called "AspectRatio" which is this:
=Width/Height
But I can't do this using a PRECEDING load after the GENERIC, or even a RESIDENT load, because
Width is in one table G.Width
And Height is in another table G.Height
Generic question: Can I do "inter-column calculations" using the columns that were pivoted out of a GENERIC LOAD?
Henric describes this technique:
Finally, I have seen many examples on QlikCommunity where a For-Next loop is used to join together all tables created by the Generic prefix, e.g.:
If I joined all tables together, I would not have separate tables G.Width, G.Height, and I think I could do =Width/Height
Is my question a good-enough reason to LEFT-JOIN all my tables together?
Is there another option?
You can also try this
GenericLoad:
LOAD * INLINE [
Key, Attribute, Value
Ball, Color, 4
Ball, Diameter, 3
Box, Height, 4
Box, Length, 3
Box, Weight, 4
Box, Width, 3
Dog, Color, 4
Dog, Weight, 3
Rod, Diameter, 4
Rod, Length, 3
];
Concatenate(GenericLoad)
LOAD
*
WHERE Value > 0;
LOAD
Key,
Attribute,
If(Previous(Key) = Key, Previous(Value)/Value) AS Value;
LOAD
Key,
'AspectRatio' AS Attribute,
Value
RESIDENT GenericLoad
WHERE MixMatch(Attribute, 'Width', 'Height')
ORDER BY Key, Attribute Desc;
Regards,
Jagan.
I guess are you doing the Width/Height in the script? If not then I guess leave it as is because as HIC mentioned there is no performance benefit for combining the table into one
Good question;
I need to do it so I can pivot the amounts back down again using CROSSTABLE.
So I have now:
Ridiculous to do a GENERIC, and then a CROSSTABLE?
Maybe I can avoid both; but then I need to do some inter-row calculation somehow;
"Get all other values for [these key columns] where the [Attribute Column]= 'Width'
Divide by all other values for [these key columns] where the [Attribute Column]= 'Height'
And enter a new row with 'AspectRatio' as [Attribute Column]:
Is that possible?
Hi,
if you go the "generic load and recombine the tables" way, then one good source surely is:
Use cases for Generic Load | Qlikview Cookbook
where Rob Wunderlich proposes this code for the recombination part:
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'Flags.*') THEN
LEFT JOIN (OrdersTable) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
hope this helps
regards
Marco
without recombination of the tables generated by a generic load, you could at first concatenate your source tables with the calculated AspectRatio attribute values:
tabAttributes:
LOAD * Inline [
Key, Column, Value
Circle1, Width, 4
Circle1, Diameter, 8
Circle1, Height, 2
Circle2, Color, 7
Circle2, Height, 3
Circle2, Width, 2.25
Circle3, Width, 5
Circle3, Height, 4
Circle3, Weight, 9
];
LOAD Key,
'AspectRatio' as Column,
Evaluate(Concat(Value,'/',Column='Width')) as Value
Resident tabAttributes
Where Match(Column,'Height','Width')
Group By Key;
tabResult:
Generic LOAD * Resident tabAttributes;
DROP Table tabAttributes;
hope this helps
regards
Marco
HI,
Try this script
GenericLoad:
GENERIC LOAD * INLINE [
Key, Attribute, Value
Ball, Color, 4
Ball, Diameter, 3
Box, Height, 4
Box, Length, 3
Box, Weight, 4
Box, Width, 3
Dog, Color, 4
Dog, Weight, 3
Rod, Diameter, 4
Rod, Length, 3
];
CombinedGenericTable:
LOAD DISTINCT Key
INLINE [
Key, Attribute, Value
Ball, Color, 4
Ball, Diameter, 3
Box, Height, 4
Box, Length, 3
Box, Weight, 4
Box, Width, 3
Dog, Color, 4
Dog, Weight, 3
Rod, Diameter, 4
Rod, Length, 3
];
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'GenericLoad.*') THEN
LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
Data_Temp:
LOAD
*,
Width/Height AS AspectRatio
RESIDENT CombinedGenericTable;
DROP TABLE CombinedGenericTable;
Data:
CrossTable(Attribute, Value)
LOAD
*
RESIDENT Data_Temp;
DROP TABLE Data_Temp;
Regards,
Jagan.
You can also try this
GenericLoad:
LOAD * INLINE [
Key, Attribute, Value
Ball, Color, 4
Ball, Diameter, 3
Box, Height, 4
Box, Length, 3
Box, Weight, 4
Box, Width, 3
Dog, Color, 4
Dog, Weight, 3
Rod, Diameter, 4
Rod, Length, 3
];
Concatenate(GenericLoad)
LOAD
*
WHERE Value > 0;
LOAD
Key,
Attribute,
If(Previous(Key) = Key, Previous(Value)/Value) AS Value;
LOAD
Key,
'AspectRatio' AS Attribute,
Value
RESIDENT GenericLoad
WHERE MixMatch(Attribute, 'Width', 'Height')
ORDER BY Key, Attribute Desc;
Regards,
Jagan.
Very clever use of EVALUATE() and CONCAT()! Thank you.
In summary, the responses were:
I think this (inter-column math) is a legitimate use-case for rejoining the GENERIC table, but I love these other creative solutions. Thank you.