topic Re: Help with SQL in QlikView

Help with SQL

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

I need to do some calculations on data and cannot have null values.

The original data set contains: Part, Date, & Qty

But not every part was sold on every date. So I split the data into two tables: one containing all possible parts the other containing all possible dates.

Now how can I create a table that contains both?

Pseudo code below:

Data:

Load

Part & '|' & Date as Part|Date,

Part,

Date,

Qty;

Table A:

Load Distinct

Part

Resident Data;

Noconcatenate

Table B:

Load Distinct

Date,

0 as ZeroQty

Resident Data;

Help needed

Table C:

Load

Part|Date

ZeroQty;

Left Join(Table C)

Load

Part|Date,

Part,

Date,

Qty;

Resident

Data;

Drop Table Data;

Noconcatenate

Final Result:

Load

Part|Date,

Part,

Date,

ZeroQty + Qty as TotalQty

Resident

Table C;

Drop Table C;

Re: Help with SQL

sunny_talwar — Tue, 05 Apr 2016 17:47:50 GMT

Do you have some sample row of data with your expected output? Not really sure what you are trying to do here

Re: Help with SQL

hobanwashburne — Tue, 05 Apr 2016 17:54:21 GMT

Start
Part	Date	Qty
A	4/1/2016	3
A	4/3/2016	1
B	4/3/2016	6

Desired Output
Part	Date	Qty
A	4/1/2016	3
A	4/2/2016	0
A	4/3/2016	1
B	4/1/2016	0
B	4/2/2016	0
B	4/3/2016	6

Re: Help with SQL

sunny_talwar — Tue, 05 Apr 2016 18:05:38 GMT

Check this out:

Table:

LOAD * Inline [

Part, Date, Qty

A, 4/1/2016, 3

A, 4/3/2016, 1

B, 4/3/2016, 6

];

MinMax:

LOAD Min(Date) as MinDate,

Max(Date) as MaxDate

Resident Table;

LET vMin = Peek('MinDate');

LET vMax = Peek('MaxDate');

DROP Table MinMax;

Join(Table)

LOAD DISTINCT Date,

Part;

LOAD Date($(vMin) + IterNo() - 1) as Date,

Part

Resident Table

While $(vMin) + IterNo() - 1 <= $(vMax);

FinalTable:

NoConcatenate

LOAD Part,

Date,

Alt(Qty, 0) as Qty

Resident Table;

DROP Table Table;

Re: Help with SQL

Anonymous — Tue, 05 Apr 2016 18:15:29 GMT

hi Scott,

let me know if Sunny's solution solved your purpose. Otherwise we can help you .

Re: Help with SQL

hobanwashburne — Tue, 05 Apr 2016 18:25:33 GMT

I apologize, I discovered an error in my data set. The dates aren't always consecutive.

Please see Below:

Input

Part	Date	Qty
A	1/1/2016	3
A	2/1/2016	1
A	3/1/2016	1
B	3/1/2016	6
B	4/1/2016	3

Output

Part	Date	Qty
A	1/1/2016	3
A	2/1/2016	1
A	3/1/2016	1
A	4/1/2016	0
B	1/1/2016	0
B	2/1/2016	0
B	3/1/2016	6
B	4/1/2016	1

Re: Help with SQL

sunny_talwar — Tue, 05 Apr 2016 18:30:48 GMT

Not sure if you date is DD/MM/YYYY or MM/DD/YYYY, but assuming its the later you can try this:

Table:

LOAD * Inline [

Part, Date, Qty

A, 1/1/2016, 3

A, 2/1/2016, 1

A, 3/1/2016, 1

B, 3/1/2016, 6

B, 4/1/2016, 3

];

MinMax:

LOAD Min(Date) as MinDate,

Max(Date) as MaxDate

Resident Table;

LET vMin = Peek('MinDate');

LET vMax = Peek('MaxDate');

DROP Table MinMax;

Join(Table)

LOAD DISTINCT Date,

Part;

LOAD Date(AddMonths($(vMin), IterNo() - 1)) as Date,

Part

Resident Table

While AddMonths($(vMin), IterNo() - 1) <= $(vMax);

FinalTable:

NoConcatenate

LOAD Part,

Date,

Alt(Qty, 0) as Qty

Resident Table;

DROP Table Table;

Re: Help with SQL

hobanwashburne — Tue, 05 Apr 2016 18:33:27 GMT

I apologize, I discovered an error in my data set. The dates aren't always consecutive.

I added a new data set below. I don't want to rely on generating dates. I would like it to use whatever values exist. The same process should result in the following:

Input

Part	Attribute	Qty
A	E	3
A	F	1
A	G	1
B	G	6
B	H	3

Output

Part	Attribute	Qty
A	E	3
A	F	1
A	G	1
A	H	0
B	E	0
B	F	0
B	G	6
B	H	3

Re: Help with SQL

Anonymous — Tue, 05 Apr 2016 18:57:41 GMT

Hi Scott,

Got confused.. if i see your output data combination of Pat A and Attribute H is not there in your input.. how could your output will show.

Re: Help with SQL

sunny_talwar — Tue, 05 Apr 2016 18:58:25 GMT

What about this:

Table:

LOAD * Inline [

Part, Attribute, Qty

A, E, 3

A, F, 1

A, G, 1

B, G, 6

B, H, 3

];

Join(Table)

LOAD Distinct Attribute as NewAttribute

Resident Table;

NewTable:

LOAD Part,

If(Attribute = NewAttribute, Attribute, NewAttribute) as Attribute,

If(Attribute = NewAttribute, Qty, 0) as Qty

Resident Table;

FinalTable:

NoConcatenate

LOAD *

Resident NewTable

Where Peek('Attribute') <> Attribute

Order By Part, Attribute, Qty desc;

DROP Tables Table, NewTable;

Re: Help with SQL

Anonymous — Tue, 05 Apr 2016 18:59:22 GMT

can you give us some more details.

Re: Help with SQL

hobanwashburne — Tue, 05 Apr 2016 19:04:16 GMT

You are correct. However, "A" is a possible part and "H" is a possible attribute and therefore a valid data point. What I am seeking is all possible combinations.

Re: Help with SQL

hobanwashburne — Tue, 05 Apr 2016 19:14:00 GMT

If we go back to thinking in terms of dates

Part	Attribute	Qty
A	E	3
A	F	1
A	G	1
B	G	6
B	H	3

is equal to

Part	Date	Qty
A	1/1/2016	3
A	2/1/2016	1
A	3/1/2016	1
B	3/1/2016	6
B	4/1/2016	3

Part A and Attribute H would be the Equivalent of Part A for the month of April. Part A and April exist, there were simply none sold.

Re: Help with SQL

sunny_talwar — Tue, 05 Apr 2016 19:16:11 GMT

Did you look at my response below? Re: Help with SQL