Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chetankumargopi
New Contributor

Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi All,

In order to provide validation dashboard let me know a Qlikview macro to compare headers of latest file vs previous file.

Regards,

Chetan

8 Replies
hirishv7
Honored Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi ,

May be like this,

Here i have compared two excels in  which there is change in one header its denoted at the front end, do check this

code:

for each vExt in 'csv', 'xlsx', 'xls'

    for each vFile in Filelist('YourPath\YourfilePrefix*.' & '$(vExt)')

        let vLen@1 = len('$(vExt)');

        let vLen@2 = len(subfield('$(vFile)', '.', -1));

        if $(vLen@1) - $(vLen@2) = 0 then

            let vFileFormat = pick(match('$(vExt)', 'csv', 'xlsx', 'xls'),

                '(txt, codepage is 1252, no labels, delimiter is '';'')',

                '(ooxml, no labels, table is Sheet1)',

                '(biff, no labels, table is @1)');

            let vConcat = if(noofrows('MetaData') > 0, 'concatenate (MetaData)', 'MetaData:');

            $(vConcat)

            First 1

            Load filebasename() as FileName, FileExtension() as FileTyp, * From $(vFile) $(vFileFormat);

        end if

    next

next

 

MetaDataCrosstable:

crosstable(Field, FieldName, 2) Load * Resident MetaData;

drop table MetaData;

exit script;

Output:

Comparision of excel header.PNG

Hope this helps,

PFA,

Regards,

Hirish

chetankumargopi
New Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi Hirish,

I understand the point mentioned, But in my case i have situation where current file can have additional columns included or also have chance of missing columns. Either of the case I need to highlight to the end user that we are receiving additional columns and missed out few columns when compared with previously provided files.

To be more specific, attached is the sample application where in column @12 & @14 is newly added to latest file received when i compare with previously received file, Here my question how to handle this case.

Please review and let me know if you need any additional details

regards,

Chetan

hirishv7
Honored Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi,

Your concern is to highlight the changed headers only right!!

-hirish

chetankumargopi
New Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Yes...Highlight the columns

And second step is to compare data between 2 files -- This we can check later.

regards,

Chetan

hirishv7
Honored Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi,

Like this,

Comparision of excel header2.PNG

HTH,

PFA,

Hirish

chetankumargopi
New Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi Hirish,

Thanks for the valuable input. I think you have provided me nearest solution. I just need to highlight  newly added column information "Establishment_code" and "Rank_Address" in current/latest file.

I am now attaching the file which i have tried till now with the inputs and suggestions by you and others. Which may give you more detailed information on the task i am working on.

In the attached qvw application 2 points have to be taken care before playing with application

1. The application provided you takes files from same path, but in my attached application it takes from 2 different locations i.e., current and previous location folders

2. If you see table object "Difference in Column Names" i am trying to achieve to highlight newly added columns.

Since i was not able to achieve in the scripting methodology, I thought  I will work with macros. So is the post title say about the macro's.

Hope these points help you.

Also please share your personal email ID.

Regards,

Chetan

hirishv7
Honored Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi ,

I have just seen the application and i came know that you need  the latest and previous files info right !!!

  • And you want to see the file and its latest time
  • if there any changes made to that particular field in a file should be notified  as a highlighted.

is this above mentioned?

-Hirish

chetankumargopi
New Contributor

Re: Qlikview macro to compare headers and data in files(csv,xls,xlsx)

Hi Hirish,

Yes, you are correct, just that basic rule is to highlight the missed or newly added column in latest file to the end user.

I have just seen the application and i came know that you need  the latest and previous files info right !!!

Chetan - Yes your understanding is Correct

  • And you want to see the file and its latest time

     Chetan - I need to see File metadata, and latest time i have used for finding the file in the previous folder.

  • if there any changes made to that particular field in a file should be notified  as a highlighted.

     Chetan - Not exactly changes, I assume that u understood @14 field 'Address" has become as "Rank_Address", if yes then the understanding is wrong bcoz for me "Rank_Address" are new column added to my file which was not in previously received file. Also vise versa also should work, If current/latest received file missing column when compared to previously received file then again i need to highlight

Hoping we are on same page.

I cant stop from thanking you, as you have understood and explained me details at the best. Thanks once again.

Regards,

Chetan

Community Browser