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‌

Thanks for your feedback. Will it be possible to share sample QVW file?

Alternatively, please try to store the mapping data to a qlikview table and than try to apply map from that. For example:

Value_Map_Stage:

SQL

select      Identifier,

               [Value(s)]

From      Table

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

Value_Map:

Mapping LOAD

select           Identifier,

                    [Value(s)]

RESIDENT      Value_Map_Stage;

Table:

LOAD*,

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

SQL

Select *

from Table;

DROP TABLE Value_Map_Stage;

Regards

Rajiv.

[Anonymous]

apologies but i do not see a file attached?

[Anonymous]

hi Rajiv when i try this one i get the following error :-

ODBC connection failed

Value_Map_Stage:

SQL

select      Identifier,

               [Value(s)]

From      Table

Where      [Field(s)] = 'product'

Unfortunately i can not attach the QV files for some reason (could be to do with permission rights at work!)  this is the scripts for both of your recommendations though :-

Value_Map_Stage:

SQL

select      Identifier,

               [Value(s)]

From      Table

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

Value_Map:

Mapping LOAD

select           Identifier,

                    [Value(s)]

RESIDENT      Value_Map_Stage;

Table:

LOAD*,

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

FROM

(ooxml, embedded labels, table is Sheet1);

SQL

Select *

from Table;

DROP TABLE Value_Map_Stage;

And the other script suggested

Value_Map:

MAPPING LOAD*,

SQL

SELECT Identifier,

Value

FROM TABLE

WHERE Field = 'product';

TABLE:

LOAD *,

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

FROM

(ooxml, embedded labels, table is Sheet1);

SQL

SELECT *

FROM TABLE;

[rajivmeher · Accepted solution]

Hi

What is the data source you are trying to access? Is it a excel file? If yes, please try the following:

Value_Map:

Mapping      LOAD

select           Identifier,

                    [Value(s)]

FROM

(ooxml, embedded labels, table is Sheet1)

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

TestTable:

LOAD *,

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

FROM

(ooxml, embedded labels, table is Sheet1);

DROP TABLE Value_Map;

Regards

Rajiv.

[trdandamudi]

You should see outside your Inbox. Don't open this thread from within your Inbox then you will not see the file.

[Anonymous]

thanks Rajiv but it still does not like the command of 'select' after Mapping LOAD, it advises its a syntax error :-

Syntax error, missing/misplaced FROM:

Value_Map:

Mapping      LOAD

select           Identifier,

                    [Value(s)]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE [Field(s)] = 'product'

Value_Map:

Mapping      LOAD

select           Identifier,

                    [Value(s)]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE [Field(s)] = 'product'

[Anonymous]

thanks Thirumala i understand and can see your attachment now. I see that you have got it working. One question though i normally like to load the data in via Input Box. after the square bracket for the Inline is that where i would ad my $ and name to link it to my Input Box or is there a better way to do this? Apologies ive never used Inline before as a statement and have appreciated yours and Rajivs persistence in getting me through this!!

[Anonymous]

I am pleased to announce i have done it. It was more to do with placement in my initial script logic but i couldnt have done this with out everyones input! thank you so much to everyones input, time and perseverance on this!!!

[rajivmeher]

Hi jfreeman‌

Sorry for the silly mistake. It was a copy paste mistake. Please see the query below with the fix.

Value_Map:

Mapping      LOAD DISTINCT

                    Identifier,

                    [Value(s)]

FROM

(ooxml, embedded labels, table is Sheet1)

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

TestTable:

LOAD           *,

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

FROM

(ooxml, embedded labels, table is Sheet1);

DROP TABLE Value_Map;