Formatting a Data for a Field in QVD - How?

[dmohanty]

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?

[maxgro]

s:

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

CODE

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

];

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;

[Kushal_Chawda]

try this

Data:

LOAD * Inline [

Input

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

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;

[#4]

Hi

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

[agustinbobba]

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

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

];

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.

[anlonghi2]

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