topic Re: Compare Field Data and Return the list in QlikView

Compare Field Data and Return the list

qlikwiz123 — Wed, 25 Nov 2020 16:16:04 GMT

I have my data coming in from CSV files as follows

Data:

Load

Order,

Date,

OrderKey,

App

from CSV1.csv

concatenate(Data)

Load

Order

from CSV2.csv

This creates a single table with Order, Date, OrderKey fields. The Orders from CSV1 and CSV2 may or may not match.

I need to show the list of all the Orders that doesn't have a Date value to them from this final table.

Re: Compare Field Data and Return the list

vamsee — Wed, 22 Aug 2018 20:28:09 GMT

Maybe try this

This creates a flag called source and you can filter accordingly based on it.

Data:

Load

Order,

'CSV1' as Source

Date,

OrderKey,

App

from CSV1.csv

concatenate(Data)

Load

Order,

'CSV2' as Source

from CSV2.csv

Thanks.

Re: Compare Field Data and Return the list

rwunderlich — Thu, 23 Aug 2018 02:54:08 GMT

Is CSV2 just a list of Orders that have some attribute? If so, shouldn't it be a separate table instead of concatenated?

-Rob

Re: Compare Field Data and Return the list

qlikwiz123 — Thu, 23 Aug 2018 13:35:04 GMT

CSV1 Has Orders and Attributes. CSV2 has only Orders that may or may not be present in CSV1. I want to check if the orders in CSV2 are present in CSV1 and if they do, display the list.

Re: Compare Field Data and Return the list

qlikwiz123 — Thu, 23 Aug 2018 14:30:48 GMT

Hi Vamsee,

My problem statement is quite different. I have two different sources (CSV1 and CSV2). While CSV1 has Order and other attribute data, CSV2 only has Order field. I need to check if the 'Order' values from CSV2 exist in CSV1 to check if any Orders in CSV2 has attribute data in CSV1 and then show those Orders.

Re: Compare Field Data and Return the list

vamsee — Fri, 24 Aug 2018 14:57:12 GMT

Hello,

Try either of these.

1: Use the same flag created above and see if any orders have both the flags.

Dimension: Orders, Expression: IF(Count( DISTINCT Source)=2, 1,0)

Data_Check:
Load
Order
from CSV1.csv;
Inner Join (Data)
Load
Order
from CSV2.csv;

Data:
Load
Order,
'CSV1' as Source,
Date,
OrderKey,
App
from CSV1.csv;
concatenate(Data)
Load
Order,
'CSV2' as Source
from CSV2.csv;

Left Join(Data)
LOAD
Order,
1 as Flag
Resident Data_Check;

Use the column Flag to determine the Orders present in both sources.