topic group table and make new columns in QlikView

group table and make new columns

qluser01 — Wed, 25 Nov 2020 16:16:04 GMT

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......

Re: group table and make new columns

vinieme12 — Sun, 05 Feb 2017 11:40:30 GMT

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

Re: group table and make new columns

vinieme12 — Sun, 05 Feb 2017 11:40:59 GMT

Re: group table and make new columns

sunny_talwar — Sun, 05 Feb 2017 12:57:05 GMT

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];

Re: group table and make new columns

qluser01 — Sun, 05 Feb 2017 16:53:17 GMT

Sorry vinieme12 too complex

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

thank you both!

Re: group table and make new columns

vinieme12 — Sun, 05 Feb 2017 17:01:20 GMT

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

Re: group table and make new columns

sunny_talwar — Sun, 05 Feb 2017 17:31:34 GMT

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)

Re: group table and make new columns

vinieme12 — Sun, 05 Feb 2017 18:21:55 GMT

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