Limited load with where statement

[carolin01]

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

[richard_pearce6]

Have you tried taking out the brackets around BaseItemTextLenght ?

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

Richard

qlikcentral | Understand / Create / Inform

[its_anandrjs]

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

[MK_QSL]

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;

[jonathandienst]

Hi

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

[its_anandrjs]

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

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

[maxgro]

if you want to filter the Length of string BaseItem

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

[carolin01]

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

[carolin01]

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.

[CELAMBARASAN · Accepted solution]

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?