Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

dsharmaqv
Contributor III

Pre Processing of file Containing value saperated by semi colon

Hi All

I have a requirement to properly format the file while loading.

Original data in file

CountryRegionProduct
India;BangladeshASPTV
India;Saudi;UKASP;EUR;MEREF
India(ASP);UK(EUR)ASP;EURAudio

Target format

CountryRegionProduct
IndiaASPTV
BangladeshASPTV
IndiaASPREF
SaudiMEREF
UKEURREF
IndiaASPAudio
UKEURAudio
1 Solution

Accepted Solutions

Re: Pre Processing of file Containing value saperated by semi colon

May be like this:

Table:

LOAD SubField(Country, '(', 1) as Country,

  Product;

LOAD SubField(Country, ';') as Country,

    Product

FROM

[https://community.qlik.com/thread/220803]

(html, codepage is 1252, embedded labels, table is @1);

7 Replies

Re: Pre Processing of file Containing value saperated by semi colon

May be this:

Table:

LOAD RowNo() as RowNo,

  Country,

  SubField(Region, ';') as Region,

  Product;

LOAD SubField(Country, ';') as Country,

     Region,

     Product

FROM

[https://community.qlik.com/thread/220803]

(html, codepage is 1252, embedded labels, table is @1);


Capture.PNG

dsharmaqv
Contributor III

Re: Pre Processing of file Containing value saperated by semi colon

Thanks for the reply.

On seeing the above result.

Saudi -----> EUR

but saudi should fall undar ME

Saudi------>ME

and

UK------->EUR

along with this

On country field values are not consistent some time it is coming as India, India(ASP), India (ASP), India(India)

I want to make it consistant and everywhere it should display in appropriate name.

Re: Pre Processing of file Containing value saperated by semi colon

It code seems to be follow the order. Not sure if you want the code to be more smart. Can you may be use mapping load if you already know that UK will always be EUR? and IND will always be ASP?

dsharmaqv
Contributor III

Re: Pre Processing of file Containing value saperated by semi colon

Yes UK will always be EUR and IND will always be ASP.

Will you be able to provide be some example for mapping load as I am new to qlikview.

Many thanks in Advance!

dsharmaqv
Contributor III

Re: Pre Processing of file Containing value saperated by semi colon

Thanks Sunny

Can we remove text within brackets

For Example:

India(ASP) should be displayed as India only, else I have to maintain multiple values in mapping table.

Regards

Re: Pre Processing of file Containing value saperated by semi colon

May be like this:

Table:

LOAD SubField(Country, '(', 1) as Country,

  Product;

LOAD SubField(Country, ';') as Country,

    Product

FROM

[https://community.qlik.com/thread/220803]

(html, codepage is 1252, embedded labels, table is @1);

Community Browser