Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! Please let me know the formula to get the string pattern of each field .
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.
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,
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