topic Re: Compare two column values and create a new column in QlikView

Compare two column values and create a new column

Anonymous — Wed, 24 Feb 2016 17:04:49 GMT

I have two columns with years 2014 and 2015.

The values present in Column 2014 may or may not be present in 2015.

I have to create a column where

If the value is present only in 2014, then the new column value must say 'Old'.

if the value is present only in 2015, then the new column value must say 'New'.

If the value is present in both the years, it must show 'Both' as Column values.

How do I get this done?

Re: Compare two column values and create a new column

cesaraccardi — Wed, 24 Feb 2016 17:19:50 GMT

Hi,

Are the 2014 / 2015 columns on the same table in your data model?

Re: Compare two column values and create a new column

Anonymous — Wed, 24 Feb 2016 17:21:41 GMT

No. The 2014 Column is in a different sheet and 2015 is in another sheet of the same excel sheet. Both 2014 and 2015 have Text values, not numbers. I need to check whether the values are present in 2014 and 2015 and create a new column based on the comparison. ( Conditions given above for comparison)

Re: Compare two column values and create a new column

Gysbert_Wassenaar — Wed, 24 Feb 2016 17:28:38 GMT

LOAD

2014

2015

If(Len(Trim(2014)>0 and Len(Trim(2015)=0, 'Old',

If(Len(Trim(2014)=0 and Len(Trim(2015)>0, 'New',

If(Len(Trim(2014)>0 and Len(Trim(2015)>0, 'Both')))

FROM

....

Re: Compare two column values and create a new column

Anonymous — Wed, 24 Feb 2016 17:36:18 GMT

how can I load data from two different sheets of the same Excel sheet using one FROM?

Re: Compare two column values and create a new column

Anonymous — Wed, 24 Feb 2016 17:37:27 GMT

Also, the above given conditions do not create a column. It just checks for the length and trim func

Re: Compare two column values and create a new column

Anonymous — Wed, 24 Feb 2016 17:47:34 GMT

The 2014 column is from Sheet 1 and 2015 column is from Sheet 2 of the same Excel sheet.

2014 2015

----- --------

1234 0112

5678 0966

0987 1234

3456 46840

Need to create a new column where the values must be:

if only in 2015 - "New"

if only in 2014 "Discarded"

if both 2014 and 2015 "Both"

Re: Compare two column values and create a new column

Gysbert_Wassenaar — Wed, 24 Feb 2016 18:56:03 GMT

You can't. One load can only load from one sheet. You need two loads at least. And it's not clear to me how a record in one sheet is related to a record in the other sheet. Can you explain that?

Perhaps you're looking for this:

Temp2014:
LOAD Value, Value as Check2014 FROM excelfile.xlsx (ooxml, embedded labels, table is Sheet2014);

Temp2015:
LOAD Value, Value as Check2015 FROM excelfile.xlsx (ooxml, embedded labels, table is Sheet2015);


Result:

LOAD 
    Value, 'Both' as NewOldBoth 
RESIDENT 
    Temp2014
WHERE Exists(Check2015, Value);


CONCATENATE (Result)

LOAD
    Value, 'Old' as NewOldBoth 
RESIDENT
    Temp2014
WHERE NOT Exists(Check2015, Value);


CONCATENATE (Result)

LOAD
    Value, 'New' as NewOldBoth 
RESIDENT
    Temp2015
WHERE NOT Exists(Check2014, Value);


DROP TABLES Temp2014, Temp2015;

Re: Compare two column values and create a new column

Anonymous — Wed, 24 Feb 2016 19:07:02 GMT

Please find my Column structure below. The 2014 column from Sheet 1 and 2015 Column from Sheet 2 could have common values.

Re: Compare two column values and create a new column

Anonymous — Wed, 24 Feb 2016 19:19:56 GMT

What is the Value in this script?

Re: Compare two column values and create a new column

Gysbert_Wassenaar — Thu, 25 Feb 2016 06:41:31 GMT

Ok, you don't seem to quite know what a load statement is. Please work through the tutorial you can find at Tutorial ‒ QlikView. When you get to Chapter 3 of the pdf file make sure to follow the instruction on top op page 97:

>>Study the script<< (emphasis added by me).

In the LOAD statement, the fields of the selected file are listed. Some of the field

names are enclosed by square brackets. This is required when a field name contains spaces. The

FROM statement is followed by the path to the file.

Re: Compare two column values and create a new column

Thu, 25 Feb 2016 12:21:22 GMT

Hi,

Can you please see the below output and let me know is this the one you are expecting? If yes, can you please see the attached script and if no, please provide the output you are expecting

Thanks,
Sreeman

Re: Compare two column values and create a new column

Anonymous — Thu, 25 Feb 2016 14:30:01 GMT

Hi Sreeman,

Thank you so much. More or less, yes. But the I should see only three columns : final_2014, final_2015 and the result column where it gives me 'New' 'Discarded' and 'Both' options. I was using conditions which should work fine but for some reason, they don't. I figured it out anyway. Thanks once again.