topic Re: Automatic match rows and columns between 2 files in QlikView

Automatic match rows and columns between 2 files

Fitus9092 — Sun, 14 Mar 2021 17:57:46 GMT

Hello i need a script that automatically reads column and rows between 2 files (2 excel or qvd for example), that must have same data, same number of rows and column. The script must:

1) Reads the columns and check if the 2 files have the same number of columns.

2) Reads the rows and check if the 2 files have the same number of rows.

3) Check if every row of the second file is the same of the first file.

Imagine the files have 80 column and 1000 rows (For example) so i can't use load in line, but i need some automatic script

that use "for each" or something similar.

Thanks Guys.

Re: Automatic match rows and columns between 2 files

MayilVahanan — Mon, 15 Mar 2021 07:03:04 GMT

Hi Fitus,

Might be try like below

FACT:
Load *, Dim&'|'&Budget&'|'&Expenses as Key Inline
[
Dim, Budget, Expenses
Jan 21, 5, 5
Feb 21, 10, 7
Mar 21, 15, 16
Apr 21, 20,
May 21, 30
];

FACT1:
NoConcatenate
Load *, Dim&'|'&Budget&'|'&Expenses as Key Inline
[
Dim, Budget, Expenses
Jan 21, 5, 5
Feb 21, 10, 7
Mar 21, 15, 16
Apr 21, 20,
May 21, 25
];

set vList=;
set vList1 = ;
set vDelimiter=CHR(13);

for i = 1 to NoOfFields('FACT')

let vFieldName = FieldName($(i), 'FACT');

let vList = '$(vList)' & $(vDelimiter) & '$(vFieldName)';

next i

for i = 1 to NoOfFields('FACT1')

let vFieldName1 = FieldName($(i), 'FACT1');

let vList1 = '$(vList1)' & $(vDelimiter) & '$(vFieldName1)';

next i

TMP:

load * inline [

COLUMN_NAME1

$(vList)

];

MapTable2:
Mapping
load *,1 as Flag inline [

COLUMN_NAME2

$(vList1)

];

COLUMNS:

noconcatenate
load COLUMN_NAME1, ApplyMap('MapTable2', COLUMN_NAME1, 0) as ColumnFlag
resident TMP
order by COLUMN_NAME1 asc;

drop table TMP;

Join(FACT)
Load Key, 1 as ValueFlag Resident FACT1;

DROP Table FACT1;

Here, ColumnFlag used to compare the column names are same or not.

ValueFlag used to compare the values are same or not.

Instead of inline, u can replace with ur source.

Hope it gives some idea for you to proceed.

Re: Automatic match rows and columns between 2 files

Fitus9092 — Mon, 15 Mar 2021 17:30:49 GMT

Many thanks for your help!