Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Intersection of data

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.

resutColsid   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

Tags (2)
3 Replies
Not applicable

Re: Intersection of data

Any one please?

Not applicable

Re: Intersection of data

Any Help please

whiteline
Honored Contributor II

Re: Intersection of data

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

];

Community Browser