Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
Contributor II
Contributor II

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

Master
Master

Hi,

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

-hirish

HirisH
“Aspire to Inspire before we Expire!”
Contributor II
Contributor II

Yes...Highlight the columns

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

regards,

Chetan

Master
Master

Hi,

Like this,

Comparision of excel header2.PNG

HTH,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Contributor II
Contributor II

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

Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
Contributor II
Contributor II

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