Inter-column expression on a GENERIC Load

[#1]

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?

[sunny_talwar]

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

[#3]

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?    

[MarcoWedel]

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

[MarcoWedel]

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

[jagan]

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.

[jagan · Accepted solution]

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.

[#8]

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

[#9]

In summary, the responses were:

1. Don't rejoin the GENERIC table together again; no other suggestions
2. 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
3. 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.