topic Re: Please help me get the string pattern of each field. in QlikView

Please help me get the string pattern of each field.

Wed, 19 Mar 2014 10:42:06 GMT

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

Re: Please help me get the string pattern of each field.

MK_QSL — Wed, 19 Mar 2014 10:43:11 GMT

What you exactly want?

Can you provide some sample data ?

Re: Please help me get the string pattern of each field.

Wed, 19 Mar 2014 11:02:03 GMT

It's part of the data profiling project Im working on. I want to determine the typical string pattern of each column. So that after getting the typical pattern, I can compare and check each value as valid or not dependending on the pattern defined for that column.

Ex. Product_detail_code, Modified_date, etc..

PURCHASE_DATE	PRODUCT_DETAIL_CODE
14-Nov-07	Others
Dec 2010	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 2010	43005270
9-Sep-13	11170032

Re: Please help me get the string pattern of each field.

MK_QSL — Wed, 19 Mar 2014 11:28:22 GMT

Something Like Below?

Load

RowNo() as NO,

PRODUCT_DETAIL_CODE,

PURCHASE_DATE as Original_PURCHASE_DATE,

Date(Alt(

Date#(PURCHASE_DATE,'DD-MMM-YY'),

Date#(PURCHASE_DATE,'MMM YYYY'),

Date#(PURCHASE_DATE,'YYYYMMDD'),

Date#(PURCHASE_DATE,'D-MM-YY'),

),'DD-MM-YYYY') as Modified_PURCHASE_DATE

Inline

[

PURCHASE_DATE, PRODUCT_DETAIL_CODE

14-Nov-07, Others

Dec 2010, 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 2010, 43005270

9-Sep-13, 11170032

];

Re: Please help me get the string pattern of each field.

Wed, 19 Mar 2014 11:47:57 GMT

Thanks Manish for quick response but thats not what I want. I want to determine the typical pattern of each field

Step 1 : Determine the typical pattern of the column. What is that formula to get the pattern

Output

formulaXYZ(PURCHASE_DATE ) ==> "dd-mm-yy"

formulaXYZ(Product_detail_code) ==> "NNNNNNNN" where n = 0 to 9 , 8 digits

Step 2: Validate each values in that column follows the right pattern

Purchase_date Remarks

Dec 2010 ==> Invalid Format

14-Nov-07 ==> Valid

Product_Detail_Code

Others ==> Invalid

10128738 ==> Valid (8 digits)

Re: Please help me get the string pattern of each field.

MK_QSL — Wed, 19 Mar 2014 11:52:13 GMT

Got it...

Sorry but I dont have idea.. May be someone can suggest...

Re: Please help me get the string pattern of each field.

jagannalla — Wed, 19 Mar 2014 12:14:29 GMT

Hi,

Hope it helps you,

LOAD *,if(IsNum(PRODUCT_DETAIL_CODE),'Valid','InValid') as ProdRemark,

If(Alt(Date#(PURCHASE_DATE,'DD-MMM-YY'),0)=0,'InValid','Valid') as PurDateRemark;

LOAD * INLINE [

PURCHASE_DATE, PRODUCT_DETAIL_CODE

14-Nov-07, Others

Dec 2010, 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 2010, 43005270

9-Sep-13, 11170032

];

If you want to check for 8 digits, use this calc if(IsNum(PRODUCT_DETAIL_CODE) and Len(PRODUCT_DETAIL_CODE)<=8,'Valid','InValid') as ProdRemark

Thanks,

Jagan

Re: Please help me get the string pattern of each field.

Thu, 20 Mar 2014 05:42:37 GMT

Thanks Jagan. Any suggestions how to get the typical pattern each column?

Re: Please help me get the string pattern of each field.

jagannalla — Thu, 20 Mar 2014 10:07:10 GMT

Rhodora - What do you mean by typical pattern each column ?

Re: Please help me get the string pattern of each field.

MarcoWedel — Thu, 20 Mar 2014 23:08:28 GMT

Hi Rhodora,

you are asking for a very complex function that most likely does not exist "off the shelve" in QlikView.

If you realy intend to implement such a thing (and you would surely be welcome to post the result) then using regular expressions might be the most promising approach.

Here are some links on using RegEx in QlikView:

hope this helps

regards

Marco

Re: Please help me get the string pattern of each field.

jagan — Fri, 21 Mar 2014 01:47:39 GMT

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.

Re: Please help me get the string pattern of each field.

rwunderlich — Fri, 21 Mar 2014 05:24:46 GMT

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,

Re: Please help me get the string pattern of each field.

MarcoWedel — Sun, 31 Aug 2014 23:17:55 GMT

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