Comparing two file(CSV,XLS or XLSX..) column header values in Qlikview

[Anonymous]

Hi All,

I have situation where in, I need to compare 2 file headers/columns/fields values row with any of these extensions .csv, .xlsx, .xls.

The reason I ask this because,

1.I need to bring a header validation in our process

2. And main concern here is we receive approximately 60-80 files and each time we need to compare headers/column names/field names of current month file versus previous month files so the reload process happens smoothly.

So I need suggestions and Ideas to achieve this task using Qlikview UX for visualization, Qlikview macro or any other methodologies.

Let me know if any more details required.

Awaiting for responses.

Regards,

Chetan

[marcus_sommer · Accepted solution]

For such a task you need to load the header from your files as data and apply then your checkings + adjustments and rename then the field  like you needed. The following code runs through two loops (for each extension and each file) to read the header as data (with First 1 statement and no labels - separating for extensions will be needed for applying the correct fileformat).

Extended are those data to the filetyp and filename - maybe you need here further informations like the rowno() or an extracting of parts from the filename maybe a date or period in YYYY-MM or similar. You might need further adjustment on the fileformat if they differ from these example.

For testings enable log-files and use in the case of errors trace-statement on the variables to see what they return. The debugger-window might be helpful, too.

The if-loop on the extensions-lenght checking might be superfluous but without I have had errors because by some files seems the extension-separation not to work and qv tried to load xlsx as xls-file and failed - and this small workaround was faster then to look for the real reason.

In a second step will be those table transformed per crosstable-statement into a normal table-structure and now should it be possible to make your checks and afterwards the adjustments - here an similar example how to rename fields per mapping: loading 2 level cross tables

Now the 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;

Hope it helped further.

- Marcus

[Anonymous]

Thanks Marcus for the solutions. Shall keep you posted if I have any issue.

[Anonymous]

Hi Marcus,

Your solution worked thanks for the help.

Regards,

CK

[deborahs]

Hello! I am stuck at a similar problem, where I have a new file getting generated everyday and getting stored on server. The next day, this file gets archived to another folder on the server and a new file is generated for that day! I'm trying to recreate a macro on excel where certain fields (example valuation, market value) are compared from current and archived files and returns few fields like date, some dimensions of the measures and the evaluated values (difference between values of 2 days). Can this solution be used for my issue too? 

Regards,

Deborah