In a table few records is coming from 'ACC' and few records is coming from 'HISTORY' we flagging it as 'History_Flag'. I want to have one flag called as ACC_Flag the condition is as follows.
For the common records I want to flag the value of 'ACC_Flag' as 'COMM' and rest of the records as 'Not exists'(In some cases some data is only available in ACC or 'HISTORY', that I want to flag it as 'Not exists').
Could you please help me out on this?
if(not isnull(ACC_ID) and not isnull(HISTORY_ID), 'COMM', 'Not exists') as ACC_Flag
drop table your_table;
rename table t1 to your_table
Where ACC_ID and HISTORY_ID are used as example for field in your table for checking their way comming.
If ACC and HISTORY are two different tables then:
You can use applymap() here, where for matching field values use COMM and for unmatched 'NOT EXISTS' by using
'COMM' AS Flag
Applymap(Table1, Key,'NOT EXISTS') AS Flag.
Now your flag will have COMM for common, 'NOT EXISTS' for uncommon field values.
'ACC' as History_flag
'HISTORY' as History_flag
<ConditionGivenBelow> as 'Sales_Flag'
Resident Table1; (Based on the below condtion)
Now I want to create a another table(Table2) using Resident load of above and want to create one more flag called as 'Sales_Flag'.
The condition of the sales is as follows.Whatever is tagged as 'ACC' will remain same and tagged as (ACC as 'Sales_Flag') and whatever that exists in Source2 that doesn't exits in Source1 excluding the common records between the source1 and source2 will remain tagged as (ACC as 'Sales_Flag')
Common records means, those records which is common in both Source1 and Source2.
Any help on this?
Hope the requiment is clear now.
Well, if we want to compare data in different rows then we would do this:
*, // write all fields expept History_flag, check_field
if(check_field=2, 'COMM', 'HISTORY') as Sales_Flag
*, // write all fields expept History_flag
count(History_flag) as check_field
* // write all fields expept History_flag
left join (Table2)
Load * resident Table1;