Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

How to separate columns

I have file as below

      

FieldIdFieldLabelFieldSectionFieldValueRecordTypeRecordId
427No of FloorsDetails1Expense65150
1160Total Square FootageDetails3282.99Expense65151
1478Parking AvailabilityDetailsNoExpense65152
1502Lease TypeDetailsMGRSExpense65153
1603LatitudeDetails-37.81763Expense65154
1604LongitudeDetails144.9575Expense

65155

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

Thanks much.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

9 Replies
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
Creator
Creator
Author

This didnt work.

MK9885
Master II
Master II

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

apthansh
Creator
Creator
Author

FieldLabelFieldValueRecordTypeRecordId

No of Floors1Expense65150
Total Square Footage3282.99Expense65150
Parking AvailabilityNoExpense65150
Lease TypeMGRSExpense65150
Latitude-37.81763Expense65150
Longitude144.9575Expense

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
Creator
Creator
Author

Hi Rob

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

FieldLabelFieldValueRecordTypeRecordId
No of Floors1Expense65150
Total Square Footage3282.99Expense65150
Parking AvailabilityNoExpense65150
Lease TypeMGRSExpense65150
Latitude-37.81763Expense65150
Longitude144.9575Expense

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator
Creator
Author

Awesome.Thank much.