Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
Hope this helps,
PFA,
Regards,
Hirish
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
Hi,
Your concern is to highlight the changed headers only right!!
-hirish
Yes...Highlight the columns
And second step is to compare data between 2 files -- This we can check later.
regards,
Chetan
Hi,
Like this,
HTH,
PFA,
Hirish
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
Hi ,
I have just seen the application and i came know that you need the latest and previous files info right !!!
is this above mentioned?
-Hirish
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
Chetan - I need to see File metadata, and latest time i have used for finding the file in the previous folder.
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