topic Re: Including only specific rows in QlikView

Including only specific rows

Mon, 14 Mar 2016 15:22:48 GMT

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

Re: Including only specific rows

ariel_klien — Mon, 14 Mar 2016 15:32:23 GMT

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

Re: Including only specific rows

sunny_talwar — Mon, 14 Mar 2016 15:34:10 GMT

May be this expression:

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

Re: Including only specific rows

Gysbert_Wassenaar — Mon, 14 Mar 2016 16:36:27 GMT

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

Re: Including only specific rows

Mon, 14 Mar 2016 20:45:24 GMT

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

Re: Including only specific rows

sunny_talwar — Mon, 14 Mar 2016 20:48:04 GMT

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

Re: Including only specific rows

Mon, 14 Mar 2016 21:00:15 GMT

Wow

Not good

The link says 18 not 14

Is there another way around this?

Re: Including only specific rows

ariel_klien — Tue, 15 Mar 2016 08:21:12 GMT

Hi,

You can convert it to text.

Text(Number) as TextNumber.....

Ariel

Re: Including only specific rows

sunny_talwar — Tue, 15 Mar 2016 08:26:01 GMT

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.

Re: Including only specific rows

Gysbert_Wassenaar — Tue, 15 Mar 2016 08:30:46 GMT

You're right: Re: Text to number Issue

Re: Including only specific rows

Tue, 15 Mar 2016 12:45:44 GMT

Thank you that helped except the text has been converted to exponential text format.

How can I convert so I show all 18 characters.

Thanks again

Re: Including only specific rows

Gysbert_Wassenaar — Tue, 15 Mar 2016 13:09:46 GMT

Perhaps using the Dual function. Dual(Text(MyNumber), MyNumber) as MyNumber

Re: Including only specific rows

Tue, 15 Mar 2016 14:17:28 GMT

Thank you but still showing as exponential text