Qlik Sense: Separating comma separated values into Quarters

[sruthi19]

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

[Gui_Approbato]

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.

[Vegar]

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? 

[Giannili]

How is This Qlik Sense Tutorial Different from others? OneVanilla

[GaryGiles]

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;

[sruthi19]

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

[Vegar]

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

[Vegar]

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

[alanwalkeer]

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

Onevanilla