topic Re: Qlik Sense: Separating comma separated values into Quarters in App Development

Qlik Sense: Separating comma separated values into Quarters

sruthi19 — Fri, 30 Oct 2020 16:56:36 GMT

Hi All,

I'm a very new user and was need within QlikSense i am able to separate data that comes in comma separate values as per below table:

Table A (current)

ID	Run
AA001	1,2
AA002	1
AA003	1,2,3
AA004	1,3
AA005	2,3
AA006	2
AA007	3

Output: Needed in the below format

Table B :

ID	Run	Quarter
AA001	1	Q32020
AA001	1	Q42020
AA001	1	Q12021
AA001	2	Q42020
AA001	2	Q12021
AA002	1	Q32020
AA002	1	Q42020
AA002	1	Q12021
AA003	1	Q32020
AA003	1	Q42020
AA003	1	Q12021
AA003	2	Q42020
AA003	2	Q12021
AA003	3	Q12021
AA004	1	Q32020
AA004	1	Q42020
AA004	1	Q12021
AA004	3	Q12021
AA005	2	Q42020
AA005	2	Q12021
AA005	3	Q12021
AA006	2	Q42020
AA006	2	Q12021
AA007	3	Q12021

In future Run values may be added - 4, 5......

Please help me out. Thanks

Re: Qlik Sense: Separating comma separated values into Quarters

Gui_Approbato — Fri, 30 Oct 2020 19:57:52 GMT

Hello,

You can use the Subfield function in your script.

Basically, just change the Run for Subfield(Run,',') as Run --> Because comma is your delimiter.

Re: Qlik Sense: Separating comma separated values into Quarters

Vegar — Sat, 31 Oct 2020 00:09:27 GMT

As Gui_Approbato is saying, you could probably use subfield() as a part of your solution. I do not completely understand the logic/relation to your desired Quarter field. Why does AA001 generate four rows in your final table and what is (if any) the relation between 1,2 and the Quarter values?

Re: Qlik Sense: Separating comma separated values into Quarters

Giannili — Mon, 02 Nov 2020 07:21:55 GMT

How is This Qlik Sense Tutorial Different from others? OneVanilla

Re: Qlik Sense: Separating comma separated values into Quarters

GaryGiles — Sat, 31 Oct 2020 12:49:17 GMT

Try the following in your load script:

Quarters:
Load * Inline [
Run, Quarter
1, Q32020
1, Q42020
1, Q12021
2, Q42020
2, Q12020
3, Q12021
];

TableB:
NoConcatenate
Load * Inline [
tID, tRun
];

for k=1 to NoOfRows('TableA')
Let kID = Peek('ID',k-1,'TableA');
Let kRun = Peek('Run',k-1,'TableA');
Let Cnt = SubStringCount('$(kRun)',',');
for i=1 to $(Cnt)+1
Concatenate (TableB)
Load
'$(kID)' as tID,
subfield('$(kRun)',',',$(i)) as tRun
AutoGenerate (1);
next i;
next k;

Join (TableB)
Load Run as tRun,
Quarter
Resident Quarters;

Re: Qlik Sense: Separating comma separated values into Quarters

sruthi19 — Sun, 01 Nov 2020 05:39:35 GMT

Here Run is nothing but Quarters where Execution need to be done.

If 1 - it has to execute in Q32020 and also next quarters , i.e Q42020, Q12021,Q22021.

If 2 - it has to execute in Q42020 and also next quarters, i.e Q12021,Q22021.

and so on......

Re: Qlik Sense: Separating comma separated values into Quarters

Vegar — Sun, 01 Nov 2020 08:06:48 GMT

I assume you are able to get the correct singel run (as you marked an earlier post as solved).

Then you can do like this.

Load ID, Run From SingleRunTransTable;

Left join

Load * Inline [

Run, Quarter

1, Q42020

1, Q12021

1, Q22021

2, Q12021

2, Q22021

3, Q12021];

Re: Qlik Sense: Separating comma separated values into Quarters

Vegar — Sun, 01 Nov 2020 09:49:38 GMT

If this is a valid solution to your r issue then the "only" part left is to dynamically generate the join table so that it does not need to be maintained manually in the INLINE script.

You could try this code snipplet:

FOR i=1 TO 3
LOAD
$(i) as Run,
'Q' & ceil(month(quartername( today(), IterNo()-1))/3) & year(quartername( today(), IterNo()-1)) as Quarter
AutoGenerate 1
While (4-$(i)) >= iterNo();
NEXT

Re: Qlik Sense: Separating comma separated values into Quarters

alanwalkeer — Sun, 07 Aug 2022 06:13:30 GMT

Exactly what I needed! You just saved me several hours. Thanks!

Onevanilla