Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hello!
t1:
noconcatenate
load
*,
if(not isnull(ACC_ID) and not isnull(HISTORY_ID), 'COMM', 'Not exists') as ACC_Flag
resident your_table;
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.
Please post an example source file and a table with the results you expect.
Hi,
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
following syntax:
Mapping Table1
LOAD Key,
'COMM' AS Flag
FROM table;
LOAD Key,
Applymap(Table1, Key,'NOT EXISTS') AS Flag.
FROM table;
Now your flag will have COMM for common, 'NOT EXISTS' for uncommon field values.
Thanks,
Apurva
Sorry couldn't attach the sample app due to security reasons. Please let me know if you didn't understood the requirement.
Thank You!
But unfortunately it didn't work out for me.
Table1:
Load *,
'ACC' as History_flag
Resident Source1;
Concatenate
Load *,
'HISTORY' as History_flag
Resident Source2;
Table2:
Load *,
<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')
Note:
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:
Table2:
noconcatenate
load
*, // write all fields expept History_flag, check_field
if(check_field=2, 'COMM', 'HISTORY') as Sales_Flag
load
*, // write all fields expept History_flag
count(History_flag) as check_field
resident Table1
group by
* // write all fields expept History_flag
;
left join (Table2)
Load * resident Table1;