topic Re: Row Level Match In Qlikview in QlikView

Row Level Match In Qlikview

rahulsingh12 — Mon, 31 Jul 2017 05:43:57 GMT

Hi,

I have two table namely A and B.

Both the tables have same number of columns and same column name.

Table A contain version1 data and Table B contains version2 data.

version Vendor Region Core Amount

1 A North North-East 100

1 B South South-West 100

version Vendor Region Core Amount

2 A East East 100

2 B South South-West 100

I want to compare row level data for each vendor ,for each version if it matches then yes else no.

Output:

version Vendor Region Core Amount

1 A North North-East 100

2 A East East 100

No No yes

1 B South South-West 100

2 B South South-West 100

Yes Yes Yes

how can I achieve this row level comparison in Qlikview?

Regards,

Re: Row Level Match In Qlikview

tresB — Mon, 31 Jul 2017 05:57:35 GMT

Where and how do you want this output to be displayed?

Re: Row Level Match In Qlikview

arvind_patil — Mon, 31 Jul 2017 06:00:06 GMT

Hi Rahul,

You can achieve this with the help of If condition

If( region= region 1, true, false) as RegionTrueFalse

create flag for all values.

Thanks,

Arvind Patil

Re: Row Level Match In Qlikview

rahulpawarb — Mon, 31 Jul 2017 06:02:34 GMT

Hello RS,

Please refer sample application attached herewith.

Hope this will help.

Regards!

Rahul Pawar

Re: Row Level Match In Qlikview

rahulsingh12 — Mon, 31 Jul 2017 06:09:48 GMT

Hi I want to see this output in a pivot or a straight table.

regards,

Rahul

Re: Row Level Match In Qlikview

rahulsingh12 — Mon, 31 Jul 2017 06:12:10 GMT

hi,

If i have just two or 3 columns to compare then it is ok to go by your method but if I have more than 250 columns to compare then writing the expression 250 times doesnot make sense.

Need to do something where it can generate it depending upon the number of column I have.

It could be that I have to do something at a script level,but not sure what

Regards,

Re: Row Level Match In Qlikview

rahulsingh12 — Mon, 31 Jul 2017 06:16:12 GMT

The if that you have written to compare the columns, is it possible to put a loop to take all the field value one by one.

Regards,

Rahul

Re: Row Level Match In Qlikview

rahulpawarb — Mon, 31 Jul 2017 06:48:51 GMT

I have written the sample code considering only two version for a vendor. I believe we can write a script to as you asked.

Regards!

Rahul Pawar

Re: Row Level Match In Qlikview

rahulsingh12 — Mon, 31 Jul 2017 07:05:05 GMT

I am new to looping in Qlikview.

How can I write a for loop to pick each column name and put it in the if statement you have written.

if I can achieve that then I don't need to write n number of if statement.

Regards,

Rahul

Re: Row Level Match In Qlikview

effinty2112 — Mon, 31 Jul 2017 07:16:54 GMT

Hi Rahul,

Try this expression in a straight table with dimension Vendor:

if(Count(distinct Region & '|' & Core & '|' &Amount)=1,'Yes','No')

Vendor	Match
A	No
B	Yes

Regards

Andrew

Re: Row Level Match In Qlikview

rahulsingh12 — Mon, 31 Jul 2017 07:26:27 GMT

hi,

i won't be able to find out exactly in which column do I have the mismatch.

Regards,

Rahul

Re: Row Level Match In Qlikview

effinty2112 — Mon, 31 Jul 2017 08:50:24 GMT

Hi Rahul,

Try this pivot table

Vendor	Field	Mismatch
A	Core	X
A	Region	X

Where

Field is the calculated dimension =if(not match($Field,'Vendor','version'),$Field)

and Mismatch = if(count(Distinct Pick(match($Field,'Amount','Core','Region'),Amount,Core,Region))>1,'X')

good luck

Andrew

Re: Row Level Match In Qlikview

rahulsingh12 — Mon, 07 Aug 2017 08:10:24 GMT

Hi Guys,

Any update on using a for loop to loop through the fields in the table.

Regards,

Rahul

Re: Row Level Match In Qlikview

balar025 — Mon, 07 Aug 2017 09:26:41 GMT

Here is the file which you can use for dynamic comparison.

Re: Row Level Match In Qlikview

rahulsingh12 — Fri, 18 Aug 2017 11:27:03 GMT

Hi,

I have attached a file which compares the data of each row with its previous value.

Regards,

Rahul