Intersection of data

Thu, 06 Sep 2012 18:08:29 GMT

Hello Everyone.

I need help creating data table.

Please see sample data below.

refdata:

LOAD * INLINE

[level0,level1,level2,level3,level4, sid,locid

tot market, Ra/ Tre / Fin, Ra Treas Fin,Ra Trading, Ra NA,1,11

tot market, Ra/ Tre / Fin, Ra Treas Fin,Ra Trading, Ra NA,3,22

tot market, Ra/ Tre / Fin, Ra Treas Fin, Fin Dk,,5,22

];

SidData:

LOAD sid as sidTemp,

gid as gidTemp1;

LOAD * INLINE

[sid, gid

1,12

1,123

3,23

3,24

5,66

5,77

5,75

];

LocData:

LOAD locid as locidTemp,

gid as gidTemp2;

LOAD * INLINE

[locid, gid

11,12

11,15

22,23

22,24

22,25

22,66

22,75

55,76

];

Find out Gid and create table of rows shown in table below.

resutCol	sid	locid	gid
Ra NA,	1	11	12
Ra NA,	3	22	23
Ra NA,	3	22	24
Fin Dk,	5	22	66
Fin Dk,	5	22	75

Gid is common (intersection) of each sid, locid combination for each row in ref data.

Result Col is if level4 is null then get one level up, if level 3 is null then get one level up. I have only 5 levels.

Ra NA is at level4 while Fin Dk at level 3.

Combination of level0&level1&level2&level3&level4&sid&locid colums make unique key.

If I have to do in oracle I would do something like this which will give me intersection of gid.

For data in (select * from refdata ) loop

Select data.level0, data.level1, data.level2, data.level3, data.level4, data.sid, data.locid, SidData.gid

From SidData

Where siddata.sid=data.sid;

INTERSECT

Select data.level0, data.level1, data.level2, data.level3, data.level4, data.sid, data.locid, LocData.gid

From LocData

Where LocData.locid=data.locid;

End loop;

Please let me know if i am missing somthing.

Thank you in advance for help

Re: Intersection of data

Fri, 07 Sep 2012 12:42:43 GMT

Any one please?

Re: Intersection of data

Fri, 07 Sep 2012 15:26:57 GMT

Any Help please

Re: Intersection of data

whiteline — Sat, 08 Sep 2012 15:45:39 GMT

refdata:
LOAD
      *, 
      if(not len(level4)=0, level4,
      if(not len(level3)=0, level3)) as resultCol ;
LOAD * INLINE
[level0,level1,level2,level3,level4, sid,locid
tot market, Ra/ Tre / Fin,     Ra Treas Fin,Ra Trading, Ra NA,1,11
tot market, Ra/ Tre / Fin,     Ra Treas Fin,Ra Trading, Ra NA,3,22
tot market, Ra/ Tre / Fin,     Ra Treas Fin, Fin Dk,,5,22
];
SidLocData:
LOAD * INLINE
[sid, gid
1,12
1,123
3,23
3,24
5,66
5,77
5,75
];
inner join(SidLocData)
LOAD * INLINE
[locid, gid
11,12
11,15
22,23
22,24
22,25
22,66
22,75
55,76
];

topic Re: Intersection of data in QlikView

Intersection of data

Re: Intersection of data

Re: Intersection of data

Re: Intersection of data