Including only specific rows

[#1]

Good morning

Hope all are well.

I have a loaded table that has the following elements:

Account        Month          Balance

123456          Oct               26000

123456          Nov              28000

123456          Dec              28500

123456          Jan               25000 

122244          Oct               27500

123321          Jan               10000

123321          Oct                12000

I have a straight table and I only want to pull rows where an account has a row in each month (Oct,Nov,Dec,Jan) and has a balance greater than 25000 in each month.

In the example above, these would be the only rows pulled:

123456          Oct               26000

123456          Nov              28000

123456          Dec              28500

123456          Jan               25000

Thank you

[ariel_klien]

HI

Try

Table:

LOAD * INLINE [

    Account, Month, Balance

    123456, Oct, 26000

    123456, Nov, 28000

    123456, Dec, 28500

    123456, Jan, 25000

    122244, Oct, 27500

    123321, Jan, 10000

    123321, Oct, 12000

];

left Join

LOAD Account,

  count(if(Balance>=25000,Month)) as CountMonth25000

Resident Table

group by Account;

New_Table:

LOAD Account,

  Month,

  Balance

Resident Table

where CountMonth25000=4;

drop Table Table;

Ariel

[sunny_talwar]

May be this expression:

=If(Count(TOTAL <Account> {<Balance = {'>=25000'}>} Account) = Count(DISTINCT TOTAL Month), Balance)

[Gysbert_Wassenaar]

Here's another option: sum({<Account={'=min(aggr(sum(Balance),Account,Month))>=25000 and count(distinct Month)=count(distinct total Month)'}>}Balance)

[#5]

Thank you this worked nicely

Also, I have a situation where I loaded a spreadsheet to my QlikView app that has an 18 digit number in one column. After the load I can't see the number in QlikView.

Any thoughts?

Thank you

[sunny_talwar]

QlikView is only able to read 14 digit number. May be try to see if this link helps as a workaround: Qlikview 18 character limit problem with large ... | Qlik Community

[#7]

Wow

Not good

The link says 18 not 14

Is there another way around this?

[ariel_klien]

Hi,

You can convert it to text.

Text(Number) as TextNumber.....

Ariel

[sunny_talwar]

I swear to got, I have seen it somewhere that it was 14. I don't know why 14 is the number that came to my mind.

[Gysbert_Wassenaar]

You're right: Re: Text to number Issue