Please help me get the string pattern of each field.

[#1]

Hi!  Please let me know the formula to get the string pattern of each field .

[jagan]

Hi,

Try like this

LOAD

  *,

  If(IsNum(PRODUCT_DETAIL_CODE),  Repeat('N', Len(PRODUCT_DETAIL_CODE)), 'Invalid') AS DETAIL_CODE_FORMAT,

  If(Alt(Date(Date#(PURCHASE_DATE, 'DD-MMM-YY')), 0) = 0, 'Invalid Format', 'DD-MMM-YY') AS DETAIL_DATE_FORMAT

INLINE [   

    PURCHASE_DATE, PRODUCT_DETAIL_CODE

    14-Nov-07, Others

    Dec-10, 10128738

    26-Feb-08, 11197529

    14-Dec-07, 11438953

    18-Nov-05, 11425786

    11-Sep-13, 11208766

    30-Aug-13, 11209023

    2-Sep-13, 11208856

    23-Oct-13, 11208959

    10-Sep-13, 11209074

    12-Feb-13, 11208907

    8-Nov-13, 11198348

    Nov-10, 43005270

    9-Sep-13, 11170032

];

Regards,

Jagan.

[rwunderlich]

I would suggest that Qlikview is the wrong tool for this exercise, You will want to use a tool that is specific for data profiling,

[MarcoWedel]

Hi,

although it's been some time since you opened this thread I would like to propose another solution:

table1:
LOAD RecNo() as RecID,
     *
FROM [http://community.qlik.com/thread/111162]
(html, codepage is 1252, embedded labels, table is @1);

FOR i = 1 to NoOfFields('table1')
  tabFields:
  LOAD $(i) as FieldNumber,
      FieldName($(i), 'table1') as FieldName
  AutoGenerate 1;
NEXT i;

tabFieldValues:
CrossTable (FieldName, FieldValue)
LOAD * Resident table1;

Left Join (tabFieldValues)
LOAD Distinct
     FieldValue,   
     Text(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
     Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
     Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
     Replace(Replace(Replace(Replace(Replace(Upper(FieldValue),
     '1','0'),'2','0'),'3','0'),'4','0'),'5','0'),'6','0'),'7','0'),'8','0'),'9','0')
     ,'B','A'),'C','A'),'D','A'),'E','A'),'F','A'),'G','A'),'H','A'),'I','A'),'J','A'),'K','A')
     ,'L','A'),'M','A'),'N','A'),'O','A'),'P','A'),'Q','A'),'R','A'),'S','A'),'T','A'),'U','A')
     ,'V','A'),'W','A'),'X','A'),'Y','A'),'Z','A')) as FieldFormat
Resident tabFieldValues;

Left Join (tabFieldValues)
LOAD Distinct
  FieldName,
  FieldFormat,
  AutoNumberHash128(FieldName, FieldFormat) as FieldFormatID
Resident tabFieldValues;

tabFieldFormats:
LOAD *,
     Text(Replace(PurgeChar(Capitalize(Replace(PurgeChar(Capitalize(Lower(FieldFormat)),'a'),'0','b')),'b'),'B','0')) as FieldFormat2;
LOAD FieldName,
     FieldFormat,
     AutoNumberHash128(FieldName, FieldFormat) as FieldFormatID,
     Count(FieldFormat) as FieldFormatCount
Resident tabFieldValues
Group By FieldName, FieldFormat;

DROP Fields FieldName, FieldFormat From tabFieldValues;

Left Join (tabFieldFormats)
LOAD FieldName,
     FieldFormat2,
     Sum(FieldFormatCount) as FieldFormat2Count
Resident tabFieldFormats
Group By FieldName, FieldFormat2;

Left Join (tabFieldFormats)
LOAD FieldName,
     Max(FieldFormatCount) as MaxFieldFormatCount,
     Max(FieldFormat2Count) as MaxFieldFormat2Count
Resident tabFieldFormats
Group By FieldName;

Left Join (tabFieldFormats)
LOAD FieldName,
     FieldFormat,
     FieldFormat2,
     Dual(If(IsValidFormat, 'Yes', 'No'), IsValidFormat) as IsValidFormat,
     Dual(If(IsValidFormat2, 'Yes', 'No'), IsValidFormat2) as IsValidFormat2;
LOAD Distinct
  FieldName,
  FieldFormat,
  FieldFormat2,
  FieldFormatCount=MaxFieldFormatCount as IsValidFormat,
  FieldFormat2Count=MaxFieldFormat2Count as IsValidFormat2
Resident tabFieldFormats;

hope this helps

regards

Marco