topic Re: How to separate columns in QlikView

How to separate columns

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to separate columns

its_anandrjs — Tue, 20 Dec 2016 18:40:43 GMT

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

Re: How to separate columns

apthansh — Tue, 20 Dec 2016 18:46:17 GMT

This didnt work.

Re: How to separate columns

MK9885 — Tue, 20 Dec 2016 18:46:24 GMT

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.

Re: How to separate columns

its_anandrjs — Tue, 20 Dec 2016 18:50:19 GMT

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

Re: How to separate columns

rwunderlich — Tue, 20 Dec 2016 19:06:36 GMT

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

-Rob

Re: How to separate columns

apthansh — Tue, 20 Dec 2016 19:08:59 GMT

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 ?

Re: How to separate columns

apthansh — Tue, 20 Dec 2016 19:13:23 GMT

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.

Re: How to separate columns

rwunderlich — Tue, 20 Dec 2016 19:20:48 GMT

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

Re: How to separate columns

apthansh — Tue, 20 Dec 2016 19:41:28 GMT

Awesome.Thank much.