Conditional Load

[Anonymous]

Hi

I am new to Qlikview. I have a table that has several columns.  I need to load only certain values and group them to 4 different values in a new field (e.g. Region) depending on the criteria in the two fields of the table that I load.

Let's say the names of two interested fields in the Table are rncid and spot_id.  Spot_id has several values (e.g. 0-256) and values in rncid field is 15.

Let's assume the criteria is if spot_id is 1,2,3,4,5,6 and rncid is 1 load the values and associate this with a value (Region 1) in a new field called Region, If spot_id is 8,12,15,19,20 and rncid is 4 then associate load these values and associate them in another value called Region 2 in the new field Region. If spot_id is 24,25,30,40,41,42 and rncid is 6 then associate load these values and associate them in another value called Region 3 in the new field Region.

Appreciate any help.

Thank you

Message was edited by: Ahmad Fikri

[sunny_talwar]

May be something like this:

LOAD spot_id,

          mcid,

          If(Match(spot_id, 1,2,3,4,5,6), mcid = 1, 'Region 1'),

          If(Match(spot_id, 8, 12, 15, 19, 20), mcid = 4, 'Region 2')

          If(Match(spot_id, 24,25,30,40,41,42), mcid = 6, 'Region 3', 'NA') as Region

FROM Source;

LOAD spot_id,

          mcid,

          If(Match(spot_id, 1,2,3,4,5,6) and mcid = 1, 'Region 1'),

          If(Match(spot_id, 8, 12, 15, 19, 20) and mcid = 4, 'Region 2')

          If(Match(spot_id, 24,25,30,40,41,42) and mcid = 6, 'Region 3', 'NA'))) as Region

FROM Source;

I added NA for when the three conditions are not met. You might have to use another condition there or leave it null.

UPDATE: Made updates based on Peter's response

[Anonymous]

Thank you Sunny

Forgot to mention also that I have huge records (under this field rncid) and I only need to partially load where rcnid values match certain values (e.g., 1,2,3,4,5,6,8,9,10,12,15,19,20,24,25,30,40,41,42, 50) and also when another field in the table (let's say this field name is device) match with value 234567, 654765, 12345 and ABC234. This way I can only load data that I need and discard unnecessary records from the data source.

How does WHERE statement look like if I want to use this?

Will it logically be placed before the above if(match ....) statement or after the above statement? does it really matter?

I am new to Qlikview and just used it Qlikview about few days ago so any hep would be very helpful.

[sunny_talwar]

May be like this:

LOAD spot_id,

          mcid,

          If(Match(spot_id, 1,2,3,4,5,6), mcid = 1, 'Region 1'),

          If(Match(spot_id, 8, 12, 15, 19, 20), mcid = 4, 'Region 2')

          If(Match(spot_id, 24,25,30,40,41,42), mcid = 6, 'Region 3', 'NA') as Region

FROM Source

Where Match(rcnid,1,2,3,4,5,6,8,9,10,12,15,19,20,24,25,30,40,41,42,50) and

Match(Device, '234567', '654765', '12345', 'ABC234');

LOAD spot_id,

          mcid,

          If(Match(spot_id, 1,2,3,4,5,6) and mcid = 1, 'Region 1'),

          If(Match(spot_id, 8, 12, 15, 19, 20) and mcid = 4, 'Region 2')

          If(Match(spot_id, 24,25,30,40,41,42) and mcid = 6, 'Region 3', 'NA'))) as Region

FROM Source

Where Match(rcnid,1,2,3,4,5,6,8,9,10,12,15,19,20,24,25,30,40,41,42,50) and

          Match(Device, '234567', '654765', '12345', 'ABC234');

UPDATE: Made updates based on Peter's response

[Peter_Cammaert]

These are either 3 separate column definitions with the first two having no name. Or - which I think is more probable - a badly constructed nested IF because of the single terminating parenthesis. I also do not understand what tne mcid = n expression is doing there in the value_true position. Shouldn't the preceding comma rather be an AND logical operator?

Peter

[sunny_talwar]

Yes, thanks for pointing those out. I will make edits in both the posts above.

[Anonymous]

Hi All,

Thank to both of you. For some reasons it did not work properly and I always got the result of the last IF statement.  e.g. only Region 3 or NA in the Region field that showed up although the values that match other regions exist in the data.

Below is the example based on some dummy data that I used to test the script. Note in this case HW_ID is the same as device in the earlier posts.

     If(Match(Spot_id,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,23,97,168,208,167,141,157,185,167,199,182,1     55,171,82,187,198,72,168,187,156,107) and (rncid = 11 or rncid = 12), 'Region 1'),

     If(Match(Spot_id, 18,19,20,21,70,71,72,73,74,75,76,77,78,79,80,81,83,84,85) and rncid = 8, 'Region 2'),

     If(Match(Spot_id, 1,8,9,25,26,27,28,29,30) and rncid = 14, 'Region 3'),

     If(Match(Spot_id, 29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54) and rncid =      15, 'Region 4','Undefined region') as Region

FROM Source

WHERE Match(HW_ID,'35231301','35353701','35353901','35419305','35419505','35419605','35437501','35714702','35841200','35872303',35162402,35393803,35625705,35714702,35842202)

and match(rncid,'1','2','4',8,11,'12','14','15')

and match([Spot_id],1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,

33,34,35,40,41,42,50,51,52,53,70,71,72,80,81,82,83,91,82,85,85,97,107,124,137,139,141,141,150,155,156,156,

157,160,161,168,169,171,173,175,181,182,184,185,187,188,193,197,198,

199,202,208,209,211);

In a related subject, apparently there are some overlapping items in the spot_id field and rncid that need to be categorized as the same Region. 

Does a statement lake this work?

f(Match(Spot_id,'Value1','value2','....') and (rncid = 11 or rncid = 12), 'Region 1'),

[Anonymous]

BTW, The same results when I put the if(match statements separately before loading  the table. Something like this:

Region_definition:

LOAD *, 

IF(match(rncid = '1,4' and [Spot_id] = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,23,97,168,208,167,141,157,185,167,199,182,155,171,82,187,198,72,

168,187,156,107),'Region1'),

IF(match(rncid = '1,4' and [Spot_id] = 18,19,20,21,70,71,72,73,74,75,76,77,78,79,80,81,83,84,85),'Region2'),

IF(match(rncid = '15' and [Spot_id] = 1,8,9,25,26,27,28,29,30),'Region3', 'undefined region') as Region    

The above also returns undefined region whereas I need to define a combination of cells and rncid to be called a specific region the the criteria met. It seems it only compares the last if match statement.

Any suggestion?

[sunny_talwar]

Ahmad, not sure how easy or difficult it is, but would you be able to pull together a sample with the expected output?

[Anonymous]

Hi,

I'd like to attach a sample file but for some reasons there is no option to attach csv or excel file.

So, let me try to elaborate the situation again and the expected result.

There some tables that I need to combine a table to cross check, but the table of interest is only table 1, which contains huge records (over 1-2 million records) that has 7 columns.  To make it easier to understand let's imagine this as a radio cellular system and we'd like to analyse the traffic volume  generated by certain region and or certain base stations.

Let' s say column A time stamp, column B is a radio cell id or spot _id  where the values range from 1-256, column C is rncid, which is is the base station serving certain group of radio cells. The regions are indicated by group of cell id and certain rncid, which is not available in the records.  I'd like to create some regions in the output based on the combination of a group of this cell id or spot_id  and rncid.  To complete the story, Cell D indicates incoming traffic, cell E indicates outgoing traffic, cell F indicates device id (serial number) and column G indicates user ID (subscription number)

A region can consist of  some overlapping cells (served by more than one rncid) which are indicated by the same cell number but always with different rncid .  For instance, region 1 is a group of cells with spot_id number 1,2,3,4,5,7,9,11,20 and rncid =1 or rncid 2 (some regions could be served by redundant base station so the analysis will also need to capture this situation) and region 2 consists of spot_id number 4,5,9,12,18,19,20,21 with rncid =3 or rncid =4   and so on so forth.

The expected output is a new field called Region where I select and filter each region of interest to show the traffic of each region. 

It is also to mention that out of these millions of data records, I need to partially download the records, which belong to limited regions and also certain type , not the whole records .  Let's say out of these 256 cell ids  I only need to download records when the cell id is 2,2,3,4,5,67,9,20,25,.110,120,130,140..........etc. and rncid equals 1,2,3,4,9,10,12 etc.  and also with certain device serial numbers (only certain type of hardware).

I had assumed when running the nested if(match script that was provided earlier the filter will work as I expected but apparently the values in the newly created field Region only shows the last region and undefined region. In other simulation it only returns undefined region where I know the combination of spot_id or cell_id and rncid for each region exists.

I also tried to add another simple table with three columns that contains the spot_id rncid and Region and expect this can be joined and select the region properly. While the scripts ran OK the result is not what I expect to see. The region mapping is  still wrong. So, not sure where the problem was. Does the where(match statement in the later part cause a problem or conflict when used with the earlier if(match?