Transform a value in a field to its own field

[Anonymous]

hi

What i want to do is take the set value of 'product' which is in the 'Field(s)' column and use this to create a new column called 'product' and populate the  'Values' for this against every set 'Identifier'

Example here is how my current data looks :-

Date	S.No	Identifier	Client1	Client2	Field(s)	Value(s)
9/1/2018	31	identifier 1	Dave	Alf	Time	T10:44:21Z
9/1/2018	31	identifier 1	Dave	Alf	client 1	Dave
9/1/2018	31	identifier 1	Dave	Alf	client 2	Alf
9/1/2018	31	identifier 1	Dave	Alf	country	Germany
9/1/2018	31	identifier 1	Dave	Alf	product	Oranges
9/1/2018	32	identifier 2	Fred	Sam	Time	T10:44:21Z
9/1/2018	32	identifier 2	Fred	Sam	client 1	Dave
9/1/2018	32	identifier 2	Fred	Sam	client 2	Alf
9/1/2018	32	identifier 2	Fred	Sam	country	Germany
9/1/2018	32	identifier 2	Fred	Sam	product	Apples
9/1/2018	33	identifier 3	Wayne	Colin	Time	T10:44:21Z
9/1/2018	33	identifier 3	Wayne	Colin	client 1	Dave
9/1/2018	33	identifier 3	Wayne	Colin	client 2	Alf
9/1/2018	33	identifier 3	Wayne	Colin	country	Germany
9/1/2018	33	identifier 3	Wayne	Colin	product	Pears
9/1/2018	34	identifier 4	Dave	Alf	Time	T10:44:21Z
9/1/2018	34	identifier 4	Dave	Alf	client 1	Dave
9/1/2018	34	identifier 4	Dave	Alf	client 2	Alf
9/1/2018	34	identifier 4	Dave	Alf	country	Germany
9/1/2018	34	identifier 4	Dave	Alf	product	Pears

I want to take the value of 'Product' in the 'Fields' column and with its value create a new column BUT ensure it is applied for each Identifier linked to that (it would almost be like Transposing just those two columns) It should therefore end up looking like this

Date	S.No	Identifier	Client1	Client2	Field(s)	Value(s)	Product
9/1/2018	31	identifier 1	Dave	Alf	Time	T10:44:21Z	Oranges
9/1/2018	31	identifier 1	Dave	Alf	client 1	Dave	Oranges
9/1/2018	31	identifier 1	Dave	Alf	client 2	Alf	Oranges
9/1/2018	31	identifier 1	Dave	Alf	country	Germany	Oranges
9/1/2018	31	identifier 1	Dave	Alf	product	Oranges	Oranges
9/1/2018	32	identifier 2	Fred	Sam	Time	T10:44:21Z	Apples
9/1/2018	32	identifier 2	Fred	Sam	client 1	Dave	Apples
9/1/2018	32	identifier 2	Fred	Sam	client 2	Alf	Apples
9/1/2018	32	identifier 2	Fred	Sam	country	Germany	Apples
9/1/2018	32	identifier 2	Fred	Sam	product	Apples	Apples
9/1/2018	33	identifier 3	Wayne	Colin	Time	T10:44:21Z	Pears
9/1/2018	33	identifier 3	Wayne	Colin	client 1	Dave	Pears
9/1/2018	33	identifier 3	Wayne	Colin	client 2	Alf	Pears
9/1/2018	33	identifier 3	Wayne	Colin	country	Germany	Pears
9/1/2018	33	identifier 3	Wayne	Colin	product	Pears	Pears
9/1/2018	34	identifier 4	Dave	Alf	Time	T10:44:21Z	Pears
9/1/2018	34	identifier 4	Dave	Alf	client 1	Dave	Pears
9/1/2018	34	identifier 4	Dave	Alf	client 2	Alf	Pears
9/1/2018	34	identifier 4	Dave	Alf	country	Germany	Pears
9/1/2018	34	identifier 4	Dave	Alf	product	Pears	Pears

Its important to view this as not each line is a seperate line item BUT every 5 lines are related to 1 item

Also not sure if its important to note that the values in 'Field(s)' WILL remain static all other fields are going to be changeable

any help gratefully received

thanks

[rajivmeher]

Hi jfreeman‌

Please see if applymap can be a solution for this?

See the code below based on your example:

Value_Map:

MAPPING LOAD

SQL

SELECT Identifier,

Value

FROM TABLE

WHERE Field = 'product';

TABLE:

LOAD *,

APPLYMAP('Value_Map', Identifier, NULL()) AS Product;

SQL

SELECT *

FROM TABLE;

On the above code I am picking unique combination of Identifier and value combination to a mapping table. Which is later applied to the whole table where their is a match with a identifier, the corresponding value will be assisned as product.

Regards

Rajiv.

[trdandamudi]

May be as below:

Data:
Load * Inline [
Date,S.No,Identifier,Client1,Client2,Fields,Values
9/1/2018, 31, identifier 1, Dave, Alf, Time, T10:44:21Z
9/1/2018, 31, identifier 1, Dave, Alf, client 1, Dave
9/1/2018, 31, identifier 1, Dave, Alf, client 2, Alf
9/1/2018, 31, identifier 1, Dave, Alf, country, Germany
9/1/2018, 31, identifier 1, Dave, Alf,product, Oranges
9/1/2018, 32 ,identifier 2, Fred, Sam, Time, T10:44:21Z
9/1/2018, 32, identifier 2, Fred, Sam, client 1, Dave
9/1/2018, 32, identifier 2, Fred, Sam, client 2, Alf
9/1/2018, 32, identifier 2, Fred, Sam, country, Germany
9/1/2018, 32, identifier 2, Fred, Sam,product, Apples
9/1/2018, 33, identifier 3, Wayne, Colin, Time, T10:44:21Z
9/1/2018, 33, identifier 3, Wayne, Colin, client 1, Dave
9/1/2018, 33, identifier 3, Wayne, Colin, client 2, Alf
9/1/2018, 33, identifier 3, Wayne, Colin, country, Germany
9/1/2018, 33, identifier 3, Wayne, Colin,product, Pears
9/1/2018, 34, identifier 4, Dave, Alf, Time, T10:44:21Z
9/1/2018, 34, identifier 4, Dave, Alf, client 1, Dave
9/1/2018, 34, identifier 4, Dave, Alf, client 2, Alf
9/1/2018, 34, identifier 4, Dave, Alf, country, Germany
9/1/2018, 34, identifier 4, Dave, Alf,product, Pears
];

MapTable:
MAPPING LOAD  
Identifier,
Values
RESIDENT Data
Where Fields='product';


NoConcatenate
Final:
Load *,
ApplyMap('MapTable', Identifier, NULL()) AS Product
Resident Data;

Drop Table Data;

[santhana]

Hi jemma freeman ,

kindly find the below script and use it will give you a desired output

STEP1:

LOAD * Inline[

Date, S.No, Identifier, Client1, Client2, Field(s), Value(s)

9/1/2018, 31, identifier 1, Dave, Alf, Time, T10:44:21Z

9/1/2018, 31, identifier 1, Dave, Alf, client 1, Dave

9/1/2018, 31, identifier 1, Dave, Alf ,client 2, Alf

9/1/2018, 31, identifier 1, Dave, Alf ,country, Germany

9/1/2018, 31, identifier 1, Dave, Alf ,product, Oranges

9/1/2018, 32, identifier 2, Fred, Sam ,Time, T10:44:21Z

9/1/2018, 32, identifier 2, Fred, Sam ,client 1, Dave

9/1/2018, 32, identifier 2, Fred, Sam ,client 2, Alf

9/1/2018, 32, identifier 2, Fred, Sam ,country, Germany

9/1/2018, 32, identifier 2, Fred, Sam ,product, Apples

9/1/2018, 33, identifier 3, Wayne, Colin, Time, T10:44:21Z

9/1/2018, 33, identifier 3, Wayne, Colin, client 1, Dave

9/1/2018, 33, identifier 3, Wayne, Colin, client 2, Alf

9/1/2018, 33, identifier 3, Wayne, Colin, country, Germany

9/1/2018, 33, identifier 3, Wayne, Colin, product, Pears

9/1/2018, 34, identifier 4, Dave, Alf, Time, T10:44:21Z

9/1/2018, 34, identifier 4, Dave, Alf, client 1, Dave

9/1/2018, 34, identifier 4, Dave, Alf, client 2, Alf

9/1/2018, 34, identifier 4, Dave, Alf, country ,Germany

9/1/2018, 34, identifier 4, Dave, Alf, product ,Pears];

LOAD Date,

S.No,

Identifier,

Client1,

Client2,

[Field(s)],

[Value(s)],

'*' AS TEMP Resident STEP1;

join

LOAD

S.No,

[Value(s)] as Product,

'*' AS TEMP Resident STEP1 where [Field(s)]='product';

DROP Table STEP1;

[Anonymous]

thanks Rajiv i will give this a go now and let you know

[Anonymous]

thanks Santhana and Thirumala my one concern with your solution is that i need to upload data to this daily and all the values under the fields will constantly change. I will also be uploading anything from 10 rows to 1million rows+ at a time so i do not think your solution is potentially going to work as step 1 includes all the current values? unless ive misunderstood how to apply this?

[trdandamudi]

Based on your question and the solution you got, I do not see any issue. Can you please try it out and let us know and we can go from there.

[Anonymous]

thanks Rajiv i was also thinking along the lines of an Apply Map but couldnt make it work. Ive tried your solution but it will not register SQL after the Mapping Load or the command 'select'. Ive taken everything literal is this correct? this is what ive entered :-

Value_Map:

Mapping LOAD

SQL

select Identifier,

[Value(s)]

From Table

Where [Field(s)] = 'product';

Table:

LOAD*,

ApplyMap('Value_Map',Identifier, NULL()) as PRODUCT;

SQL

Select *

from Table;

[Anonymous]

hi, i have tried but after inputing LOAD * Inline it does then not like the [ - i have never used 'Inline' before is there an easier way to enter the fields and data? also when i come to reload new data in and the values change and the volume of data changes will QV know to automtically update this table in the script? many thanks for your help

[trdandamudi]

I don't see that issue on my side and attached is the file:

Your question:

also when i come to reload new data in and the values change and the volume of data changes will QV know to automtically update this table in the script?

The answer is "Yes".