topic Re: Formatting a Data for a Field in QVD - How? in QlikView

Formatting a Data for a Field in QVD - How?

dmohanty — Wed, 11 Nov 2015 17:56:53 GMT

Hi All,

In a QVD, I have two fields/columns - FieldName and FieldValue.

In FieldName, there are lot of values including a value called 'CODE'.

The corresponding values in FieldValue column for CODE is like this below -

"10135015210"|"10310003313"|"10310022213"|"10310022240"|"10310028340"|"10310033940"|"135007003"|"135009441"|"135009626"|"135009826"|"223172001"|"223172101"|"536001583"|"536002583"|"713016512"|"7610011907"|"904000414"|"904198261"|"904198861"|"904590135"|"904772714".

I want to format the data in the column FieldValue. If the numbers between each bar ( | ) has a length less than 11, prefix with zeros (0) to make the length 11.

Now the output I want is like this below -

"10135015210"|"10310003313"|"10310022213"|"10310022240"|"10310028340"|"10310033940"|"00135007003"|"00135009441"|"00135009626"|"00135009826"|"00223172001"|"00223172101"|"00536001583"|"00536002583"|"00713016512"|"07610011907"|"00904000414"|"00904198261"|"00904198861"|"00904590135"|"00904772714"

Changes are in Bold Red - rest all remain same. The count of Leading Zeros varies - just to make the length 11.

Could someone help here please?

Re: Formatting a Data for a Field in QVD - How?

maxgro — Wed, 11 Nov 2015 18:36:53 GMT

load replace(CODE, '"', '') as CODE inline [

CODE

];

ss:

load SubField(CODE, '|') as NEWCODE, rowno() as ID

Resident s;

sss:

load '"' & Concat(NEWCODE2, '"|"', ID) & '"' as NEWCODE;

load right('00000000000' & NEWCODE, 11) as NEWCODE2, ID Resident ss;

DROP Table ss;

Re: Formatting a Data for a Field in QVD - How?

Kushal_Chawda — Wed, 11 Nov 2015 19:15:00 GMT

try this

Data:

LOAD * Inline [

Input

Data1:

load RowNo() as Row, num(subfield(PurgeChar(Input,'"'),'|'),'00000000000') as Input2

Resident Data;

Final:

LOAD

'"'&Concat(DISTINCT Input2,'"|"',Row) & '"' as Output

Resident Data1 ;

DROP Table Data1;

Re: Formatting a Data for a Field in QVD - How?

Wed, 11 Nov 2015 20:32:57 GMT

Try this

Test:

LOAD * INLINE [

FieldName, FieldValue

Code, '"10135015210"|"10310003313"|"10310022213"|"10310022240"|"10310028340"|"10310033940"|"135007003"|"135009441"|"135009626"|"135009826"|"223172001"|"223172101"|"536001583"|"536002583"|"713016512"|"7610011907"|"904000414"|"904198261"|"904198861"|"904590135"|"904772714"'

];

Test2:

NoConcatenate LOAD

FieldName,

SubField(FieldValue, '|') as FieldValuenew

Resident Test;

Left Join Load

FieldName,

'"' & repeat('0', (11 - len(TextBetween(FieldValuenew, '"', '"')))) & TextBetween(FieldValuenew, '"', '"') & '"' as FieldValuenew2

Resident Test2;

Left join ([Test])

LOAD

FieldName,

Concat(FieldValuenew2, '|') as FieldValuenew

Resident Test2

Group by FieldName;

Drop Table Test2;

Regards,

Neethu

Re: Formatting a Data for a Field in QVD - How?

agustinbobba — Wed, 11 Nov 2015 21:00:30 GMT

Hi,

You can use different functions, but the key is the Repeat function to add the 0's missing..

The first LOAD create a table with all the codes.
Repeat('0', (11 - len(Code))) add 0's if need it.
Concat(Repeat('0', (11 - len(Code))) & Code,'"|"') concat all the codes with the middle separator ' "|" ' .
'"' & and & '"' add the first and last quotes.

Aux:

LOAD * INLINE [

Codes

];

Codes:

Load

'"' & Concat(Repeat('0', (11 - len(Code))) & Code,'"|"') & '"' AS NewCode

;

Load

rowno() as RowNo,

SubField(PurgeChar(Codes,'"'), '|') as Code

Resident Aux

;

DROP Table Aux;

If there's anything you need, don't hesitate to ask.

Agustín.

Re: Formatting a Data for a Field in QVD - How?

anlonghi2 — Wed, 11 Nov 2015 21:35:20 GMT

Hi DMohanty,

please try using the following code into your script changing the name of the source.

Tmp:
LOAD FieldName,
FieldValue,
iterno() as MyIdx,
IF(FieldName='CODE',
if(len(subfield(FieldValue, '|' , IterNo()))<13,Replace(subfield(FieldValue, '|' , IterNo()),subfield(FieldValue, '|' , IterNo()),'"' & repeat('0',13-len(subfield(FieldValue, '|' , IterNo()))) & right(subfield(FieldValue, '|' , IterNo()),len(subfield(FieldValue, '|' , IterNo()))-1)),
subfield(FieldValue, '|' , IterNo())),'|') as FieldValue_sub
FROM
SumDist.xlsx
(ooxml, embedded labels, table is Sheet2) while IterNo()<=(substringcount( FieldValue , '"|')+1) ;

Final_Table:
load FieldName,
if(FieldName='CODE', concat(FieldValue_sub,'|',MyIdx),concat(FieldValue)) as FieldValue
resident Tmp group by FieldName;
drop field MyIdx;
drop Table Tmp;

Best regards

Andrea