topic Re: Generate mission rows in QlikView

Generate mission rows

Fri, 04 Dec 2015 16:33:25 GMT

Hello,

I want to generate missing rows on a table but i don't know if it's possible or not - (in script)

here is my problems :

1: I want to generate missing rows for this table :

Table 1 :

Quarter	supplier
2015_Q1	S1
2015_Q1	S2
2015_Q2	S1
2015_Q2	S3
2015_Q2	S4
2015_Q3	S1
2015_Q3	S3
2015_Q3	S5

Expected result :

Quarter	supplier
2015_Q1	S1
2015_Q1	S2
2015_Q2	S1
2015_Q2	S2
2015_Q2	S3
2015_Q2	S4
2015_Q3	S1
2015_Q3	S2
2015_Q3	S3
2015_Q3	S4
2015_Q3	S5

Thanks

Re: Generate mission rows

maxgro — Fri, 04 Dec 2015 18:11:47 GMT

LOAD Quarter,

replace(supplier, 'S', '') as snum

FROM

[https://community.qlik.com/thread/194194]

(html, codepage is 1252, embedded labels, table is @1);

S1:

load Quarter, Min(snum) as mins, Max(snum) as maxs

Resident S

Group by Quarter;

NoConcatenate

load Quarter, 'S' & supplier as supplier;

load

Quarter,

mins + IterNo()-1 as supplier

Resident S1

While mins + IterNo()-1 <= maxs;

DROP Table S, S1;

Re: Generate mission rows

swuehl — Fri, 04 Dec 2015 18:16:39 GMT

Not sure how you determine if a supplier needs to be added. Is it by the other suppliers in that quarter (like in the other solution), or by the first appearance of a supplier?

Then maybe:

Quarters:
LOAD Dual(Quarter, recno()) as Quarter, Quarter as QText INLINE [
Quarter
2015_Q1
2015_Q2
2015_Q3
];

Map:
MAPPING
LOAD QText, Quarter Resident Quarters;

INPUT:
LOAD *,
    if(not exists(supplier), Quarter) as SupplierStart;
LOAD Applymap('Map', Quarter) as Quarter,
    supplier
FROM
[https://community.qlik.com/thread/194194]
(html, codepage is 1252, embedded labels);

TMP:
LOAD supplier, SupplierStart
Resident INPUT
WHERE SupplierStart;

JOIN LOAD 3 as SupplierEnd
AutoGenerate 1;

RESULT:
LOAD Peek('Quarter', SupplierStart + iterno()-2,'Quarters') as Quarter,
  supplier
Resident TMP
WHILE SupplierStart+iterno()-1 <= SupplierEnd;

DROP TABLE TMP, INPUT, Quarters;

Lot of this codes is needed because I needed to transform Quarters to dual values for easier iterating and looping.

Re: Generate mission rows

Sun, 06 Dec 2015 23:58:59 GMT

thank you.

For suppliers S1, S2...it was just an example, my suppliers are like this :

BR_T1_CE_1263453

CA_E1_3453476

FR_T1_613453

FR_T1_703453

IN_RMX_334534

IN_RMX_334535

MA_TONE_1034535

Re: Generate mission rows

Mon, 07 Dec 2015 00:09:58 GMT

hello,

thank you.

You're right, I determinate if the supplier need to be added by his first ! !http:// appearence.

your script work well, but I mus't put the list of quarters and the number of quarter manually :

LOAD Dual(Quarter, recno()) as Quarter, Quarter as QText INLINE [

Quarter

2013_Q3

2013_Q4

2014_Q2

2014_Q3

2014_Q4

2015_Q1

2015_Q2

];

and

JOIN LOAD 7 as SupplierEnd

AutoGenerate 1;

I try to generate list of quarters but it doesn't work.

thanks

Re: Generate mission rows

swuehl — Mon, 07 Dec 2015 17:31:50 GMT

You can generate your list of quarters and max number of quarters like:


QuartersTMP:
LOAD DISTINCT Quarter
FROM
[https://community.qlik.com/thread/194194]
(html, codepage is 1252, embedded labels);


Quarters:
LOAD Dual(Quarter, recno()) as Quarter, Quarter as QText;
LOAD Quarter Resident QuartersTMP 
ORDER BY Quarter;


Let vMaxQuarter = Num(Peek('Quarter',-1,'Quarters'));


DROP TABLE QuartersTMP;


Map:
MAPPING
LOAD QText, Quarter Resident Quarters;


INPUT:
LOAD *,
     if(not exists(supplier), Quarter) as SupplierStart;
LOAD Applymap('Map', Quarter) as Quarter,
     supplier
FROM
[https://community.qlik.com/thread/194194]
(html, codepage is 1252, embedded labels);


TMP:
LOAD supplier, SupplierStart
Resident INPUT
WHERE SupplierStart;


JOIN LOAD $(vMaxQuarter) as SupplierEnd
AutoGenerate 1;


RESULT:
LOAD Peek('Quarter', SupplierStart + iterno()-2,'Quarters') as Quarter,
  supplier
Resident TMP 
WHILE SupplierStart+iterno()-1 <= SupplierEnd;


DROP TABLE TMP, INPUT, Quarters;

Re: Generate mission rows

Wed, 09 Dec 2015 14:52:32 GMT

Perfect.

I add a little modification : I order Table 1 before using it on INPUT Table.

thanks