topic Re: Inter-column expression on a GENERIC Load in QlikView

Inter-column expression on a GENERIC Load

Wed, 23 Mar 2016 23:06:55 GMT

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?

Re: Inter-column expression on a GENERIC Load

sunny_talwar — Wed, 23 Mar 2016 23:13:32 GMT

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

Re: Inter-column expression on a GENERIC Load

Wed, 23 Mar 2016 23:43:09 GMT

Good question;

I need to do it so I can pivot the amounts back down again using CROSSTABLE.

So I have now:

Key, Column, Value
Circle, Width, 4
Circle, Height, 2
Circle, AspectRatio, 2

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]:

Circle, Width, 4
Circle, Height, 2
Circle, AspectRatio, 2

Is that possible?

Re: Inter-column expression on a GENERIC Load

MarcoWedel — Thu, 24 Mar 2016 00:53:40 GMT

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

Re: Inter-column expression on a GENERIC Load

MarcoWedel — Thu, 24 Mar 2016 01:30:57 GMT

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

Re: Inter-column expression on a GENERIC Load

jagan — Thu, 24 Mar 2016 01:50:44 GMT

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.

Re: Inter-column expression on a GENERIC Load

jagan — Thu, 24 Mar 2016 02:02:03 GMT

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.

Re: Inter-column expression on a GENERIC Load

Thu, 24 Mar 2016 04:51:32 GMT

Very clever use of EVALUATE() and CONCAT()! Thank you.

Re: Inter-column expression on a GENERIC Load

Thu, 24 Mar 2016 04:54:31 GMT

In summary, the responses were:

Don't rejoin the GENERIC table together again; no other suggestions
Don't rejoin the GENERIC table together again; can achieve the technique using
1. PREVIOUS() on a subset of the table, from @Jagan
2. EVALUATE() on a subset of the table, from @Marco
Do rejoin the GENERIC table together so you can do inter-column math

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.