How to separate columns

[apthansh]

I have file as below

FieldId	FieldLabel	FieldSection	FieldValue	RecordType	RecordId
427	No of Floors	Details	1	Expense	65150
1160	Total Square Footage	Details	3282.99	Expense	65151
1478	Parking Availability	Details	No	Expense	65152
1502	Lease Type	Details	MGRS	Expense	65153
1603	Latitude	Details	-37.81763	Expense	65154
1604	Longitude	Details	144.9575	Expense	65155

Is there a way to separate Latitude and Longitude from FieldLabel and make those as 2 separate columns ?

Thanks much.

[its_anandrjs]

Try this way in the script

LOAD

     FieldId,

     FieldLabel,

     FieldSection,

     FieldValue,

     RecordType,

     RecordId,

    If(match(FieldLabel,'Latitude','Longitude'),FieldLabel) as LatLong,

     If(Not match(FieldLabel,'Latitude','Longitude'),FieldLabel) as NotLatLong

FROM

Source;

Regards,

Anand

[apthansh]

This didnt work.

[MK9885]

Yes.

Create 2 new measure....

if(FieldLabel='Latitude', 'Latitude') and name the Label as Latitude

if(FieldLabel='Longitude', 'Longitude') and name the Label as Longitude

It will create new columns but all the rows will be null and only the rows matching Latitude & Longitude will show same values.

And if you use

if(FieldLabel='Latitude', 'Latitude',FieldLabel ) will give you Latitude for latitude row and rest of the rows will show same data as FieldLabel but you'll just have a new Column.

[its_anandrjs]

What is the out put for the new field let me know.

Will it be only Latitude , Longitude  on the field if so then it is correct

Show your out put please.

Regards

Anand

[rwunderlich]

Do you want that Lat and Long assigned to every row?

-Rob

[apthansh]

	FieldLabel		FieldValue	RecordType	RecordId
	No of Floors		1	Expense	65150
	Total Square Footage		3282.99	Expense	65150
	Parking Availability		No	Expense	65150
	Lease Type		MGRS	Expense	65150
	Latitude		-37.81763	Expense	65150
	Longitude		144.9575	Expense	65150

Above is the original table and I want the out put as below.

Latitude         Longitude     RecordType   RecordID

-37.81763      144.9575      Expense         65150

It can ignore all other columns and rows.Latitude and Longitude value is coming from FieldValue column.Cross table should work for this ?

[apthansh]

Hi Rob

Below is the original table and I want the out put as below.

	FieldLabel		FieldValue	RecordType	RecordId
	No of Floors		1	Expense	65150
	Total Square Footage		3282.99	Expense	65150
	Parking Availability		No	Expense	65150
	Lease Type		MGRS	Expense	65150
	Latitude		-37.81763	Expense	65150
	Longitude		144.9575	Expense	65150

Latitude         Longitude     RecordType   RecordID

-37.81763      144.9575      Expense         65150

It can ignore all other columns and rows.Latitude and Longitude value is coming from FieldValue column.Cross table should work for this ?

Thank you very much.

[rwunderlich · Accepted solution]

What you are looking at is a Generic load.  However, since it only involves two generic fields I would find it easier to hard code it like this:

Final:

LOAD

    FieldValue as Latitude,

    RecordType,

    RecordId

FROM

[https://community.qlik.com/thread/244001]

(html, codepage is 1252, embedded labels, table is @3)

Where FieldLabel = 'Latitude';

JOIN (Final)

LOAD

    FieldValue as Longitude,

    RecordId

FROM

[https://community.qlik.com/thread/244001]

(html, codepage is 1252, embedded labels, table is @3)

Where FieldLabel = 'Longitude';

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[apthansh]

Awesome.Thank much.