CODEWITHSUNDEEP
arraysweb-scrapinggoogle-sheets-formulasubstitutiongoogle-query-language
Brijesh
Brijesh

Reputation: 51

Conditional removal of rows in importhtml data output

=ARRAYFORMULA(IFERROR(
substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"*","")*1,
substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"*","")))

where D2 = MARUTI and B2 = 30Jun2022 let's say...

Now I want to remove the row in which all columns value is zero.

Upvotes: 0

Views: 155

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

Try

    =query(ARRAYFORMULA(IFERROR(substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"*",""), substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"",""))),
"where "&ARRAYFORMULA(TEXTJOIN(" and ",, "Col"&SEQUENCE(11,1,1,1)&" is not null")),1)

Upvotes: 1

player0
player0

Reputation: 1

use:

=QUERY(ARRAYFORMULA(IFERROR(
 SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"table",1),"*",)*1,
 SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"table",1),"*",))),
 "where "&TEXTJOIN(" and ", 1, "Col"&SEQUENCE(11)&" <>0"))

enter image description here

Upvotes: 1

Related Questions