How to use the values in same field multiple times in one expression?

[#1]

Hi

I have a field with ($) values connected to a second field with the descriptions. Ie descriptions can be income, taxes, rent, funding.

All data are connected to specific individuals (PersonID)

How can I do expression with  PersonId as dimension and how much funding the have. But only for people that have a income < 5000 and rent > 2000?

My problem is that the $-values are all in the same field and I don´t want to create individual fieldvalues for rent, income, funding in the script.

I´m Think I have to use the concat functions some how but can´t get it work.

Any suggestions?

Edit:

Attached a sample data

Id / person 28  income of 9014, a rent 5808 and funding of 3320.

Id / person 1089 income of 12988, rent 5406 and funding 191

Id / person 1790 income of 7564, no funding (null- value) and rent 4098

By the 3 person I want a expression that shows the two latest by the criteria income > 5000, funding < 1000 and rent > 2000

Sorry for not uploaded a sample at the first post. And a bit changed criteria, it´s only a example.

Thanks for all feedback and help!

[mdmukramali]

Dear,

can you attach the sample File.

Thanks,

[MK_QSL]

Can you provide sample data for this question... Would like to work on this..... Thanks

[jonathandienst]

Hi

I suggest that you upload a sample qvw or at least a sample of your data. As you have seen, selecting and analysing data that is structured this way is complex and it is usually better to transform the data to a more manageable form in your load script.

I would try something like this:

// Initial load from source or qvd

T_Data:

LOAD PersonID,

  ...other fields...

  If(Description = 'funding', Amount) As Funding,

  If(Description = 'income', Amount) As Income,

  If(Description = 'taxes', Amount) As Taxes,

  If(Description = 'rent', Amount) As Rent,

  If(Match(Description, 'funding', income', 'taxes', 'rent') = 0, Amount) As Other

From SourceData;

// Transform load to consolidate rows

Data:

NoConcatenate

LOAD PersonID,

  ...other fields except Amount...

  Sum(Funding) As Funding,

  Sum(Income) As Income,

  Sum(Taxes) As Taxes,

  Sum(Rent) As Rent,

  Sum(Other) As Other

Resident T_Data

Group By

  PersonID,

  ...other fields except Amount...;

Drop Table T_Data;

Now you can use simple and intuitive analysis like:

Sum({<Rent = {">2000"}, Income = {"<5000"}>} Funding)

HTH

Jonathan

[#5]

is there no other option to do as jonathan suggest? Ok, I works but then I will get much more fields. I't looks niceer with only one valuefield insted of mutiple ones. In case the update of my fiirst post did't get to the streams, I have uploaded a sample data.

[MarcoWedel]

Hi,

if you want to keep the combined value, description fields, then just generic load the funding, rent and income field additionally:

=Sum({$<Income={"<10000"}, Rent={">2000"}>} Funding)
=Only({$<Income={">5000"}, Rent={">2000"}>-$<Funding={">1000"}>} AutonrID)

tabTest:
LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/602934-123659/Test.qvd.xml] (qvd);

tabValueType:
Generic LOAD
  AutonrID,
  Description,
  Value
Resident tabTest;

hope this helps

regards

Marco

[IAMDV]

Hi,

Do you want to test on all three conditions or just two:

Income > 5000 and Funding < 1000 and Rent > 2000

or is it EITHER of them?

Also, based on your sample data. What is the expected result?

Thanks,

DV

www.QlikShare.com

[#8]

the goal is to test on all three conditions. But if it's to any help it would also be of intrest to get the result of only two, ie income and funding.

BAsed on the sample data it should be id 1089 and 1790 that gets listed. Notice that id 1790 don't have any value for funding. To be able to do the calculation for that cases maybe the value should be set to 0 in the script?

[MarcoWedel]

without Link due to moderation status of first post:

-----------------------------------------------------------------------------------------------

Hi,

if you want to keep the combined value, description fields, then just generic load the funding, rent and income field additionally:

=Sum({$<Income={"<10000"}, Rent={">2000"}>} Funding)

=Only({$<Income={">5000"}, Rent={">2000"}>-$<Funding={">1000"}>} AutonrID)

tabTest:
LOAD * FROM [Test.qvd.xml] (qvd);

tabValueType:
Generic LOAD
  AutonrID,
  Description, 
  Value
Resident tabTest;

hope this helps

regards

Marco

[#10]

WIth your help I have found the load prefix generic load to be the key to solve this. Thanks for the feedback