topic Re: Creating Buckets on Multiple Data Type in QlikView

Creating Buckets on Multiple Data Type

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

I have attached data with different data Types. I would like to create Buckets in Load Script. I would Like to Avoid using If For each condition e.g. if(D.O.B is between X and Y then) as this Might result in me creating a Hundred Lines of Ifs which will be difficult to manage.

I would Have preferred to create a spreadsheet or Load In Line my Buckets Parameters.

Like in this instance will need to created buckets for

D.O.B

Ave Income

Family Size

Re: Creating Buckets on Multiple Data Type

sunny_talwar — Tue, 27 Jun 2017 11:15:45 GMT

Are you looking for something like this may be?

The Generic Load

Re: Creating Buckets on Multiple Data Type

ngrunoz — Tue, 27 Jun 2017 12:54:59 GMT

Hi Sunny

I was trying to avoid Generic Load at it might create a lot of Tables. All I want to do is create a second table that will have Question as Key and then it will match all the responses if the category to give a Bucket almost in a similar way IntervalMatch works.

Re: Creating Buckets on Multiple Data Type

sunny_talwar — Tue, 27 Jun 2017 13:08:32 GMT

Try this

Table:

LOAD CitizenCode,

Question,

Response

FROM

[..\..\Downloads\Citizen Info With Parameters.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

FinalTable:

LOAD Distinct CitizenCode

Resident Table;

FOR i = 1 to FieldValueCount('Question')

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

Left Join (FinalTable)

LOAD Distinct CitizenCode,

Response as [$(vField)]

Resident Table

Where Question = '$(vField)';

NEXT

DROP Table Table;

Re: Creating Buckets on Multiple Data Type

ngrunoz — Tue, 27 Jun 2017 13:38:00 GMT

Hi Sunny. I am sorry. I am failing to express it.

The result end of will be e.g below.

And I would like to do this for all my Value responses. on all the different questions. And the reason also for trying to maintain it in one column is to try and enable it to be dynamic on the chart as this will need to be changed by one filter for Questions. and the Questions will amount to over 500 questions. I am not sure If what I am trying to do makes sense.

Response	Bucket
22/22/2017 12:01 PM	1990 - 2020
12/23/1969	1960 - 1970
12/23/1979	1970 - 1980
05/05/1993	1990 - 2020
01/01/2017 19:51	1990 - 2020
02/22/2017	1990 - 2020
03/11/2017	1990 - 2020
05/05/2017	1990 - 2020