Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

In Script, How to Select Field that is Not Blank?


Hi Team,

I am trying to load from Excel in QV Script.  I only want records that have a SHPD_DT that is NOT blank.  That is, I want only those items that have shipped.  How do I do this?  With a Where statement?  What is the proper syntax?

ABC as [ABC No Dashes],
    
SHPD_QTY,
    
Date(SHPD_DT, 'M/D/YY') as [Ship Date],
    
YEAR(Date(SHPD_DT,'M/D/YY')) as [Ship Yr],
    
MONTH(Date(SHPD_DT,'M/D/YY')) as [Ship Mo],
     1001
as Site,
    
COUNTRY_CD,
    
TAC1_CITY,
    
TAC1_LINE1,
    
TAC1_LINE2,
    
TAC1_STATE,
    
TAC1_STREET_ADDR,
    
TAC1_ZIP5 as [Zip Code]
FROM

(
ooxml, embedded labels, table is [NSN Shipments 2013], filters(
Remove(Col, Pos(Top, 68)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2))
));

This is probably easy.  I just cannot find the syntax anywhere.

Thanks for your help.

Tags (2)
1 Solution

Accepted Solutions
jpapador
Valued Contributor

Re: In Script, How to Select Field that is Not Blank?

You would do it at the end before the semicolon like this:


ABC as [ABC No Dashes],
    
SHPD_QTY,
    
Date(SHPD_DT, 'M/D/YY') as [Ship Date],
    
YEAR(Date(SHPD_DT,'M/D/YY')) as [Ship Yr],
    
MONTH(Date(SHPD_DT,'M/D/YY')) as [Ship Mo],
     1001
as Site,
    
COUNTRY_CD,
    
TAC1_CITY,
    
TAC1_LINE1,
    
TAC1_LINE2,
    
TAC1_STATE,
    
TAC1_STREET_ADDR,
    
TAC1_ZIP5 as [Zip Code]
FROM

(
ooxml, embedded labels, table is [NSN Shipments 2013], filters(
Remove(Col, Pos(Top, 68)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2))
))

WHERE Len(SHPD_DT) > 0;

3 Replies
jpapador
Valued Contributor

Re: In Script, How to Select Field that is Not Blank?

You would do it at the end before the semicolon like this:


ABC as [ABC No Dashes],
    
SHPD_QTY,
    
Date(SHPD_DT, 'M/D/YY') as [Ship Date],
    
YEAR(Date(SHPD_DT,'M/D/YY')) as [Ship Yr],
    
MONTH(Date(SHPD_DT,'M/D/YY')) as [Ship Mo],
     1001
as Site,
    
COUNTRY_CD,
    
TAC1_CITY,
    
TAC1_LINE1,
    
TAC1_LINE2,
    
TAC1_STATE,
    
TAC1_STREET_ADDR,
    
TAC1_ZIP5 as [Zip Code]
FROM

(
ooxml, embedded labels, table is [NSN Shipments 2013], filters(
Remove(Col, Pos(Top, 68)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2))
))

WHERE Len(SHPD_DT) > 0;

Not applicable

Re: In Script, How to Select Field that is Not Blank?

Try if(NOT isnull(SHPD_DT) or if(SHPD_DT <> '')

Not applicable

Re: In Script, How to Select Field that is Not Blank?

This worked, thanks!

Community Browser