How to load part of field name as attribute?

[gshockxc]

I have a list of data like this:

Well_1_Lateral_psi

Well_1_temp_T1

Well_1_casing_psi

Well_1_flow

Well_1_choke_setting

Well_1_temp_T2

Well_1_dP

Well_1_h1

Well_1_h2

Well_1_massflow

Well_1_BTU_hr

Well_1_HP

Well_2_Lateral_psi

Well_2_temp.

Well_2_casing_psi

Well_2_flow

I want to load the first six charachters of each field name as the new value for the attribute "WellName".  I can't figure out how to do this properly.  I have tried a Generic Load, which results in many synthetic keys.  I have also tried Text(Left(fieldname,6)), which only seems to load the values in the field, not the fieldname, itself.

Thank you in advance for any help you can offer.

[MarcoWedel]

please provide some sample data and your expected result

[rwunderlich]

This strikes me as a Generic Load problem. Here's a sample script. You did not provide the value column so I faked in "10".

Wells:
Generic LOAD
  left(Attribute,6) as WellName,
  mid(Attribute,8),
  10
Inline [
Attribute
Well_1_Lateral_psi
Well_1_temp_T1
Well_1_casing_psi
Well_1_flow
Well_1_choke_setting
Well_1_temp_T2
Well_1_dP
Well_1_h1
Well_1_h2
Well_1_massflow
Well_1_BTU_hr
Well_1_HP
Well_2_Lateral_psi
Well_2_temp.
Well_2_casing_psi
Well_2_flow
];

More on this kind of problem here: https://qlikviewcookbook.com/2010/05/use-cases-for-generic-load/

As @hic has pointed out here https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470, there is no typically no need to join the generic tables into one big table. But if you want to, the QlikView Cookbook post shows a script pattern for joining the tables. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[gshockxc]

Rob,

     Part of your response, I understand.  The other part, I don't, only because I'm unfamiliar with the Generic Load.  I researched it before posting, but I wasn't sure that I understood the application.  In this case, the Well Name is the only thing I'm interested in.  I don't think I have a "value" to use for the load statement.

My goal is to load something similar to the below:

Wells:
Generic LOAD
  left(Attribute,6) as WellName,

Well_1_Lateral_psi
Well_1_temp_T1
Well_1_casing_psi
Well_1_flow
Well_1_choke_setting
Well_1_temp_T2
Well_1_dP
Well_1_h1
Well_1_h2
Well_1_massflow
Well_1_BTU_hr
Well_1_HP
Well_2_Lateral_psi
Well_2_temp.
Well_2_casing_psi
Well_2_flow

...

From ... ;

In the Wells table, I would have something like this:

Well_1

Well_2

Well_3

In the App, I could select any one of the 5 wells, and view data only for that Well, i.e. Well 3.

[gshockxc]

Marco,

     Thanks for the reply.  As I mentioned below, my objective is only to load the well names so that the respective data can be filtered based on that attribute.  That is, the names of the wells would be loaded as values under the new field name "Wells", or similar.

Here's my sample data:

Well_1_temp_T1

Well_1_casing_psi

Well_1_flow

Well_1_choke_setting

Well_1_temp_T2

Well_1_dP

Well_1_h1

Well_1_h2

Well_1_massflow

Well_1_BTU_hr

Well_1_HP

Well_2_Lateral_psi

Well_2_temp.

Well_2_casing_psi

Well_2_flow

Well_2_choke_setting

Well_2_temp_T2

Well_2_dP

Well_2_h1

Well_2_h2

Well_2_massflow

Well_2_BTU_hr

Well_2_HP

Well_3_Lateral_psi

Well_3_temp.

Well_3_casing_psi

Well_3_flow

Well_3_choke_setting

Well_3_temp_T2

Well_3_dP

Well_3_h1

Well_3_h2

Well_3_massflow

Well_3_BTU_hr

Well_3_HP

Well_4_Lateral_psi

Well_4_temp.

Well_4_casing_psi

Well_4_flow

Well_4_choke_setting

Well_4_temp_T2

Well_4_dP

Well_4_h1

Well_4_h2

Well_4_massflow

Well_4_BTU_hr

Well_4_HP

Well_5_Lateral_psi

Well_5_temp.

Well_5_casing_psi

Well_5_flow

Well_5_choke_setting

Well_5_temp_T2

Well_5_dP

Well_5_h1

Well_5_h2

Well_5_massflow

Well_5_BTU_hr

Well_5_HP

My intended result is to have a table called WellNames, with values Well_1, Well_2, Well_3, Well_4, and Well_5.

I could do an inline load, but I want to standardize the load script so that I can replicate it for other cases where part of the field name is used as the value in a new field.

Thanks in advance,

[gshockxc]

I tried to follow the Generic Load concept, but I have never used this before.

WellName:
Generic Load

Left(Well_1_temp_T1,6)                                                                  As WellName,         //Expected result is 'Well_1'
Right(Well_1_temp_T1,Len(Well_1_temp_T1)-6)                As WellAttribute,   //Expected result is 'temp_T1'
Mid(Left(Well_1_temp_T1,5),Left(Well_1_temp_T1,7))    As WellNumber      //Expected result is '1'

From ...

But it's not generating the table, and I'm not getting any errors in the script.

[rwunderlich]

The sample data you have posted. Are those column names in your data? Or field values?

-Rob

[gshockxc]

Rob, 

Thanks for the reply.  Those are the column names.  

[rwunderlich]

If I understand your data correctly, this may be a CrossTable followed by Generic load. You will have a synthetic key, but it is a valid synkey.

Input:
CrossTable (Attribute, Data, 1)
LOAD RecNo() as RecId, * Inline [
Well_1_Lateral_psi, Well_1_casing_psi, Well_1_flow, Well_2_Lateral_psi, Well_2_casing_psi, Well_2_flow
5, 10, 20, 35, 45, 50
9, 18, 22, 87, 88, 89
]
;

Wells:
Generic LOAD
RecId,
left(Attribute,6) as WellName,
mid(Attribute,8),
Data
Resident Input;
Drop Table Input;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com