Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
apthansh
Contributor

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
MVP & Luminary
MVP & Luminary

Re: How to separate columns

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

Re: How to separate columns

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
Contributor

Re: How to separate columns

This didnt work.

MK9885
Honored Contributor II

Re: How to separate columns

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

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

MVP & Luminary
MVP & Luminary

Re: How to separate columns

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

-Rob

apthansh
Contributor

Re: How to separate columns

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
Contributor

Re: How to separate columns

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.

MVP & Luminary
MVP & Luminary

Re: How to separate columns

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

apthansh
Contributor

Re: How to separate columns

Awesome.Thank much.