Compare two column values and create a new column

[Anonymous]

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?

[cesaraccardi]

Hi,

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

[Anonymous]

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)

[Gysbert_Wassenaar]

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

     ....

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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"

[Gysbert_Wassenaar]

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;

[Anonymous]

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

[Anonymous]

What is the Value in this script?