Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
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

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

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

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.