topic Re: Limited load with where statement in QlikView

Limited load with where statement

carolin01 — Wed, 02 Apr 2014 15:00:06 GMT

Hi,

I´m trying to do a limited resident load from another table with the following statemtent:

where mid(BaseItem, 1, 2) = 'EH' and (BaseItemTextLenght <=20);

But for some reason it only reduces the data to Base-Item EH, it does not fullfull the second condition that the BaseItemTextLength should be <= 20. What am I doing wrong?

Best regards

Carolin

Re: Limited load with where statement

richard_pearce6 — Wed, 02 Apr 2014 15:02:07 GMT

Have you tried taking out the brackets around BaseItemTextLenght ?

where mid(BaseItem, 1, 2) = 'EH' and BaseItemTextLenght <=20;

Richard

qlikcentral | Understand / Create / Inform

Re: Limited load with where statement

its_anandrjs — Wed, 02 Apr 2014 15:03:09 GMT

In the BaseItemTextLenght it contains 20 or less value please check.

Re: Limited load with where statement

MK_QSL — Wed, 02 Apr 2014 15:05:41 GMT

TRY

where mid(BaseItem, 1, 2) = 'EH' and BaseItemTextLenght <=20;

SAMPLE DATA GIVEN BELOW... Please check...

SALES:

Load * Inline

[

Customer, Sales

A, 100

B, 200

C, 300

D, 340

E, 400

];

NoConcatenate

Load * Resident SALES

Where Match(Customer,'A','C','D') and Sales >= 200;

Drop Table SALES;

Re: Limited load with where statement

jonathandienst — Wed, 02 Apr 2014 15:05:57 GMT

Some possibilities:

Is the spelling wrong or is that a typo: BaseItemTextLenght or BaseItemLength?
I assume that BaseItemTextLength is a field. Or did you mean that the length of BaseItem should be less than 20? In which case you need ...And Len(BaseItem) < = 20
Both of these fields must exist in the source table for the LOAD statement.
Are there any BaseItemTextLength values > 20?

HTH

Jonathan

Re: Limited load with where statement

its_anandrjs — Wed, 02 Apr 2014 15:18:54 GMT

Remove opening and closing brakets will solve the problem in the below line.

where mid(BaseItem, 1, 2) = 'EH' and BaseItemTextLenght <=20;

Re: Limited load with where statement

maxgro — Wed, 02 Apr 2014 18:49:49 GMT

if you want to filter the Length of string BaseItem

where mid(BaseItem, 1, 2) = 'EH' and len(BaseItem) <=20;

Re: Limited load with where statement

carolin01 — Thu, 03 Apr 2014 10:18:47 GMT

OK, I think I know what the issue is but still don´t know how to avoid it. I´m using a backward load and the where statement is not working for the second (upper) part of the load. The Len(BaseItem) is the correct function. But what / Where do I need to implement in the second (upper) part?

Load*,
if(DESIGN_OP = 'M', 'flat face M18x1,5 rod 10 mm',
      if(DESIGN_OP = 'S', 'flat face 3/4-16 rod 10 mm',
           if(DESIGN_OP = 'L', 'flat face 3/4-16 rod 7 mm',
                if(DESIGN_OP = 'K', 'flat face M18x1,5 rod 7mm',
                     if(DESIGN_OP = 'W', 'flat face M18x1,5, 316L, 10mm',
                          'New Design or Invalid Design Option'))))) as DESIGN_OP_DESC,
if(Connection_Type = 'D84','8-pin M12 connector',
      if(Connection_Type = 'D34','5-pin M12 connector',
           if(Connection_Type = 'M01' or 'M00','Molex connector',
                if(Connection_Type = 'D60','6-pin DIN connector',
                     if(Connection_Type = 'E62','2x6 DIN Connector CAN + pwr',
                          if(Connection_Type = 'D62','2x6 pin DIN CAN',
                               'Invalid Connection Type')))))) as Connection_Type_Desc,
if(Options = '1','24 V Standard',
      if(Options = 'A','24 V HVO',
           'Invalid Option')) as Options_DESC;

Load
      BaseItem,
      mid(BaseItem, 1, 2) as Design,
      'Standard rod version' as HousingType,
      mid(BaseItem, 3, 1) as DESIGN_OP,
      '' as MAGNET_TYPE,
      '' as MAGNET_TYPE_DESC,
      mid(BaseItem, 4, 4) as LENGTH,
      mid(BaseItem, 8, 1) as LENGTH_DESC,
      mid(BaseItem, 9, 3) as Connection_Type,
      '' as CableLenght,
      mid(BaseItem, 12, 1) as Options,
      mid(BaseItem, 13, 6) as POS_OUT
Resident dProductsLud
Where mid(BaseItem, 1, 2) = 'EH' and len (BaseItem) <=20;

Thanks for your help in advance!

Best regards, Carolin

Re: Limited load with where statement

carolin01 — Thu, 03 Apr 2014 10:20:28 GMT

I implemented the field BaseItemTextLenght into the first table from which I do the resident load. This function would for this reason also work but it does not work in the second part of my backward load --> see description in my answer below.

Re: Limited load with where statement

CELAMBARASAN — Thu, 03 Apr 2014 10:31:34 GMT

Hi,

Looks strange. I dont think its possible, because you script seems to be good.

Load*,
if(DESIGN_OP = 'M', 'flat face M18x1,5 rod 10 mm',
      if(DESIGN_OP = 'S', 'flat face 3/4-16 rod 10 mm',
           if(DESIGN_OP = 'L', 'flat face 3/4-16 rod 7 mm',
                if(DESIGN_OP = 'K', 'flat face M18x1,5 rod 7mm',
                     if(DESIGN_OP = 'W', 'flat face M18x1,5, 316L, 10mm',
                          'New Design or Invalid Design Option'))))) as DESIGN_OP_DESC,
if(Connection_Type = 'D84','8-pin M12 connector',
      if(Connection_Type = 'D34','5-pin M12 connector',
           if(Connection_Type = 'M01' or 'M00','Molex connector',
                if(Connection_Type = 'D60','6-pin DIN connector',
                     if(Connection_Type = 'E62','2x6 DIN Connector CAN + pwr',
                          if(Connection_Type = 'D62','2x6 pin DIN CAN',
                               'Invalid Connection Type')))))) as Connection_Type_Desc,
if(Options = '1','24 V Standard',
      if(Options = 'A','24 V HVO',
           'Invalid Option')) as Options_DESC;
Load
      BaseItem,

     len (BaseItem) AS BaseItemTextLength,//Add this line
      mid(BaseItem, 1, 2) as Design,
      'Standard rod version' as HousingType,
      mid(BaseItem, 3, 1) as DESIGN_OP,
      '' as MAGNET_TYPE,
      '' as MAGNET_TYPE_DESC,
      mid(BaseItem, 4, 4) as LENGTH,
      mid(BaseItem, 8, 1) as LENGTH_DESC,
      mid(BaseItem, 9, 3) as Connection_Type,
      '' as CableLenght,
      mid(BaseItem, 12, 1) as Options,
      mid(BaseItem, 13, 6) as POS_OUT
Resident dProductsLud
Where mid(BaseItem, 1, 2) = 'EH' and len (BaseItem) <=20;

Add the line

len (BaseItem) AS BaseItemTextLength,//Add this line

to your script as above and check the values in list box whether it has values >20?

Re: Limited load with where statement

jonathandienst — Thu, 03 Apr 2014 10:39:12 GMT

Carolin

I don't understand what the problem is. The script looks OK to me. The upper part of the load uses the bottom part as the source, so it will only act on the data that has already passed your filter.

In what way is it not working? Remember that unless you drop table dProductsLud, all the BaseItems will still exist in the model, but only those that passed the filter will have values in DESIGN_OP or any of the other derived fields in either part of the load statement.

Jonathan

Re: Limited load with where statement

Thu, 03 Apr 2014 10:51:11 GMT

Hi,

Instead of len (BaseItem) <=20 use len (trim(BaseItem)) <=20 in where clause

Re: Limited load with where statement

its_anandrjs — Thu, 03 Apr 2014 11:09:15 GMT

There is no error in your load script try to make another resident load and make and instead of using this script and len (BaseItem) <=20; make a field len (BaseItem) as Len_BaseItem

Temp:

     len(trim(BaseItem)) AS Len_BaseItem, ///New field add here
      mid(BaseItem, 1, 2) as Design,
      'Standard rod version' as HousingType,
      mid(BaseItem, 3, 1) as DESIGN_OP,
      '' as MAGNET_TYPE,
      '' as MAGNET_TYPE_DESC,
      mid(BaseItem, 4, 4) as LENGTH,
      mid(BaseItem, 8, 1) as LENGTH_DESC,
      mid(BaseItem, 9, 3) as Connection_Type,
      '' as CableLenght,
      mid(BaseItem, 12, 1) as Options,
      mid(BaseItem, 13, 6) as POS_OUT
Resident dProductsLud;

Drop table dProductsLud;

Noconcatenate

New:

Load

DESIGN_OP_DESC,Connection_Type_Desc

BaseItem,

Len_BaseItem,

Design,

HousingType,

DESIGN_OP,

MAGNET_TYPE,

MAGNET_TYPE_DESC,

LENGTH,

LENGTH_DESC,

Connection_Type,

CableLenght,

Options,

POS_OUT

Resident Temp

Where Design = 'EH' and Len_BaseItem <=20;

New update please check

Re: Limited load with where statement

carolin01 — Thu, 03 Apr 2014 14:27:05 GMT

This helped me to see that most of my values only hat 12 positions. Also I had to change from < to >. Poor me, I´m always confused by the direction of that sign

mid(BaseItem, 1, 2) = 'EH' and len (trim(BaseItem)) >=12;

Re: Limited load with where statement

carolin01 — Thu, 03 Apr 2014 14:38:28 GMT

By the way, can anybody explain what 'trim' in that statement means? I use it now but I don´t know if it is important.

Re: Limited load with where statement

its_anandrjs — Thu, 03 Apr 2014 14:41:51 GMT

Trim function removes Blank space from the field suppose field contains spaces see the example

Eg:-

trim ( ' abc' ) returns 'abc'

trim ( 'abc ' ) returns 'abc'

trim ( ' abc ' ) returns 'abc'

Re: Limited load with where statement

carolin01 — Thu, 03 Apr 2014 14:53:47 GMT

Very useful then. I also feared that this could be an option.