Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qluser01
Creator
Creator

group table and make new columns

Hi all!

I have a table like:

RandomData:

Load * Inline [

Date, Nature, Amount

08.01.2017, %paid, 3000

08.01.2017, loan repaid, 5000

10.12.2016, %paid, 3000

10.12.2016, loan repaid, 7000

05.11.2016, %paid, 3000

05.11.2016, loan repaid, 9000

03.10.2016, %paid, 3000

03.10.2016, loan repaid, 5000

10.09.2016, %paid, 3000

10.09.2016, loan repaid, 5000

]

I need to

1) Group it by Date (or maybe distinct by date)

2) distribute Amount to new columns -> [%paid] and [loan repaid]

3) make a new column - [overpaid] where show amounts larger than 5000 (excess)

So the result should be:

Date, %paid, loan repaid, overpaid

08.01.2017, 3000, 5000, 0

10.12.2016, 3000, 5000, 2000

05.11.2016, 3000, 5000, 4000

03.10.2016, 3000, 5000, 0

10.09.2016, 3000, 5000, 0

]

Please, help me with that......

1 Solution

Accepted Solutions
sunny_talwar

Here is another option

SET DateFormat='DD.MM.YYYY';

RandomData: 

Load * Inline [ 

Date, Nature, Amount 

08.01.2017, %paid, 3000 

08.01.2017, loan repaid, 5000 

10.12.2016, %paid, 3000 

10.12.2016, loan repaid, 7000 

05.11.2016, %paid, 3000 

05.11.2016, loan repaid, 9000 

03.10.2016, %paid, 3000 

03.10.2016, loan repaid, 5000 

10.09.2016, %paid, 3000 

10.09.2016, loan repaid, 5000 

];

tmpTable:

LOAD Distinct Date

Resident RandomData;

For i = 1 to FieldValueCount('Nature')

  LET vField = FieldValue('Nature', $(i));

  TRACE $(vField);

  Left Join (tmpTable)

  LOAD Distinct Date,

  Amount as [$(vField)]

  Resident RandomData

  Where Nature = '$(vField)';

NEXT

FinalTable:

LOAD *,

  5000 as [loan repaid new],

  [loan repaid] - 5000 as overpaid

Resident tmpTable;

DROP Tables RandomData, tmpTable;

DROP Field [loan repaid];

RENAME Field [loan repaid new] to [loan repaid];

Capture.PNG

View solution in original post

7 Replies
vinieme12
Champion III
Champion III

RandomData: 

Generic Load * Inline [ 

Date, Nature, Amount 

08.01.2017, %paid, 3000 

08.01.2017, loan repaid, 5000 

10.12.2016, %paid, 3000 

10.12.2016, loan repaid, 7000 

05.11.2016, %paid, 3000 

05.11.2016, loan repaid, 9000 

03.10.2016, %paid, 3000 

03.10.2016, loan repaid, 5000 

10.09.2016, %paid, 3000 

10.09.2016, loan repaid, 5000 

];

Set vListOfTables = ;

  For vTableNo = 0 to NoOfTables()-1

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='RandomData' Then

        Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

  Next vTableNo

CombinedGenericTable:

  LOAD Distinct Date  Inline [ 

Date, Nature, Amount 

08.01.2017, %paid, 3000 

08.01.2017, loan repaid, 5000 

10.12.2016, %paid, 3000 

10.12.2016, loan repaid, 7000 

05.11.2016, %paid, 3000 

05.11.2016, loan repaid, 9000 

03.10.2016, %paid, 3000 

03.10.2016, loan repaid, 5000 

10.09.2016, %paid, 3000 

10.09.2016, loan repaid, 5000 

];

  For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

  Next vTableName

After this Resident Load CombinedGenericTable and create your new column of overpaid as you want to

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Also read

The Generic Load

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

Here is another option

SET DateFormat='DD.MM.YYYY';

RandomData: 

Load * Inline [ 

Date, Nature, Amount 

08.01.2017, %paid, 3000 

08.01.2017, loan repaid, 5000 

10.12.2016, %paid, 3000 

10.12.2016, loan repaid, 7000 

05.11.2016, %paid, 3000 

05.11.2016, loan repaid, 9000 

03.10.2016, %paid, 3000 

03.10.2016, loan repaid, 5000 

10.09.2016, %paid, 3000 

10.09.2016, loan repaid, 5000 

];

tmpTable:

LOAD Distinct Date

Resident RandomData;

For i = 1 to FieldValueCount('Nature')

  LET vField = FieldValue('Nature', $(i));

  TRACE $(vField);

  Left Join (tmpTable)

  LOAD Distinct Date,

  Amount as [$(vField)]

  Resident RandomData

  Where Nature = '$(vField)';

NEXT

FinalTable:

LOAD *,

  5000 as [loan repaid new],

  [loan repaid] - 5000 as overpaid

Resident tmpTable;

DROP Tables RandomData, tmpTable;

DROP Field [loan repaid];

RENAME Field [loan repaid new] to [loan repaid];

Capture.PNG

qluser01
Creator
Creator
Author

Sorry too complex

Sunny T's solution is simple and clear as always ))


thank you both!

vinieme12
Champion III
Champion III

Not a problem buddy

Can you just try both methods with your data and let us know the reload times for both.

Just keen to know

Thanks

V

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

I agree with the testing part... I never got a chance to test this, but I have always found Generic Load to be fairly complex. I mean you break them into different table and then use complex For Loop logic to join them back. Why not just use For Loop upfront. But having said that, I am sure there ought to be some performance difference (when there is lots of data at hand, for smaller databases, I wouldn't see much difference)

vinieme12
Champion III
Champion III

I second what you said Sunny ;  I think the determining factor would be how FieldValue() performs.

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.