Discussion Board for collaboration regarding Qlik DataMarket.
I have a file which is not in apprpriate formate and I want to change the file as per the reporting requirement.
Data is not in order due to missing values.
In first record
Region City and trader name is missing.
Please help me out in finding the solution.
What is the logic behind finding which field is missing.
Unfortunately, there is no logic behind that. The only thing is that all the value appears in series if velue is there
Business Function/Region/Country/Portfolio/City/Trader Name.
So from above data how system should know what value is what. For example you said from above data(1st Case) region is missing, how system should know, because system will consider IND as region because its second value.
Hope u understood what I want to say.
I understood your question and have posted this query to look for some work around to get the desiderd result.
you can try this (Not given exact solution).
Please go around with FindOneOf( and SubStringCount( like that ..
What i found is , if you have count of character '/" greater than 5 times only we can able to split...
(html, codepage is 1252, embedded labels, table is @1);
SubStringCount(FullPath,'/') as char_count,
FindOneOf(FullPath,'/') as exactplace,
FindOneOf(FullPath,'/',2) as exactplace2,
Len(FullPath) as fullpathlen
DROP Table SOURCE;
if( char_count>=5, LEFT(FullPath,exactplace-1)) as BusinessFuntion,
if( char_count>=5, MID(FullPath,exactplace,(exactplace2- exactplace))) as Region
DROP TABLE TEMP1;