Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qliksus
Specialist II
Specialist II

File Format Issue

Hi ,

I have a excel which have data lke

RegionNameRegionCode
AABO1 1,AB01 2,AC02 1,DB01 2 etc

No i want the output as

RegionNameRegioncode
AABO1 1
AAB01 2
AAC02
ADB01 2
A

Can anyone help me to sorted out?

Thanks in Advance.

3 Replies
swuehl
MVP
MVP

Try

LOAD

     RegionName,

    subfield( RegionCode, ',') as RegionCode

FROM YourTable;

Not applicable

Hi,

you can do a SubField in the script:

A:

LOAD RegionName,

    RegionCode

FROM

C:\Users\ferrdani\Desktop\Community\Regions.xlsx

(ooxml, embedded labels, table is Sheet1);

B:

NoConcatenate

Load

    RegionName,

    SubField(RegionCode,',') as RegionCode

resident A;

Drop table A

Attached an example

qliksus
Specialist II
Specialist II
Author

Hi,

I have been using substring function for a long time, but never imagine it can be used without offset which will work as above requirement. Thanks for reminding mine old days to do exercise on functions again.

Thanks.