Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Are you looking for something like this may be?
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.
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;
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 |