DATA Update

[#1]

i want to load data for Domain ,SubDomain,Visit

1.for Domain i want to load only those rows which is not eqal to total .

2.for SubDomain i want to load rows which is not equal to total but if count of Domain is equal to 1 then i also need to load total for that row .

is there any way to do this.

Sample data:

Domain	SubDomain	Visit
abc	abc1	10
abc	abc1	10
abc	abc1	10
abc	abc1	10
abc	total	50
xyz	total	20
ghj	ghj1	30
ghj	ghj1	30
ghj	ghj1	30
ghj	ghj1	30
ghj	total	120
jkl	total	80
total	total	430

Load table should be like as:

Domain	SubDomain	Visit
abc	abc1	10
abc	abc1	10
abc	abc1	10
abc	abc1	10
xyz	total	20
ghj	ghj1	30
ghj	ghj1	30
ghj	ghj1	30
ghj	ghj1	30
jkl	total	80

[#2 · Accepted solution]

try this:

test:

LOAD * INLINE [

    Domain, SubDomain, Visit

    abc, abc1, 10

    abc, abc1, 10

    abc, abc1, 10

    abc, abc1, 10

    abc, total, 50

    xyz, total, 20

    ghj, ghj1, 30

    ghj, ghj1, 30

    ghj, ghj1, 30

    ghj, ghj1, 30

    ghj, total, 120

    jkl, total, 80

    total, total, 430

];

test2:

load Domain,  count(Visit) as Counter resident test group by Domain;

left join (test) load * resident test2;

drop table test2;

final:

load Domain, SubDomain, Visit resident test where SubDomain<> 'total' or Counter=1 and Domain <>'total';

drop table test;

[jvitantonio]

Hi, you can also do it directly in the pivot table. Check out the app.